Userform VBA - fetch data from table, editable, save

**sqrcbc** · 09-14-2017, 10:03 AM

Hi all,

so I got an Userform[1] in which i want to get the values from a table[2], and if needed edit and save them.

The first field requires a identification number to be entered. (1-1-1-0001, e.g.)
Then on click on "Daten abrufen" the rest of the empty textfields should be filled with the data which are connected to the entered value above.

The, in my opinion, main problem with editing the values is, the identification number is "generated" with "Raum" "Regal" "Regalsegment" (Room, Rack, Racksegment) followed by a 000X.
The reason for this number is, to easy and accelerate the process of finding the according place to look for or put the item.

So I would need some sort of check for "uniqueness" of the identification number, and a appropriate behavoir...

Please Login or Register  to view this content.

this is how the identification string is generated, if it helps in any way...

Anyway, I managed to get a Form up and running to add all that data to a table.
But somehow I dont find a working way to call up on them and edit them...

Any help is much appreciated <3

[1]: https://i.stack.imgur.com/tRCky.png
[2]: https://i.stack.imgur.com/Xcvce.png

**bakerman2** · 09-14-2017, 10:09 AM

Is it possible to post an example file with your userform and some lines of data we can work with. (don't forget to desensitize data)

**sqrcbc** · 09-14-2017, 10:21 AM

of course,

but please bear in mind, i am not a coder, nor i do that in any professional way. (the file might be a mess in regards of the code^^)
It's some kind of project i want to support a local homeless shelter with...

since I cant physically attach the file to this post,

HTML Code:

**bakerman2** · 09-14-2017, 09:37 PM

This should do it.

**sqrcbc** · 09-18-2017, 03:00 AM

Hello Bakerman2,

Sorry for my late response!
Yes, in deed, you pretty much nailed it, thank you very much.
I added some Feedback like

Please Login or Register  to view this content.

Since I think that might help those people who aren't that firm with computers and stuff in general.
And yes, they still exist, these dinosaurs :D

Is there any chance you could help me with one last thing? <3

**bakerman2** · 09-18-2017, 08:18 AM

If I'm able to, why not.

**sqrcbc** · 09-18-2017, 10:19 AM

To be quite honest, I am totally lost with adding a search form...
I would like to have a Userform which allows to find all entries containing "the search term(s)" and/ or the year (each entered in a separate field).
Plus: Is it possible to enter multiple search terms? separated by commas?

HTML Code:

Since I can't upload directly... :/

Thanks in advance for your support ^-^
-----------------------------------------------------------

Please Login or Register  to view this content.

This is how far I've gotten, and it kind of works.
Now is it possible to add another textfield as an optional search condition?
The search now is "Name only" and i would like to add the possibility to filter these by another value (Column "E")

**bakerman2** · 09-20-2017, 12:58 PM

sqrcbc,

Didn't forget about you but have been too busy the last few days.
I have some ideas and will get back to you asap.

Also can you post example file with some data in so I can test. (Testfile goes well but like to test on real data)

Sorry.

**sqrcbc** · 10-10-2017, 05:47 AM

I sure can upload a file, yet i cannot include proper data
the only Thing i can include is bulk lorem ipsum stuff^^

Will update the file in the link above, and repost it.
in roughly 5-6 hours tho, sorry

**bakerman2** · 10-10-2017, 07:28 AM

Open vbeditor and open searchform.
You have the option to search for Suchbegriffe only, Jahre only or combination of both.

**sqrcbc** · 10-10-2017, 09:31 AM

Here you got the latest version of my file
with my personal progress on the searchform

HTML Code:

p.s.

could you maybe have a look at the edit form
i somehow messed up the edit/save function...
it doesnt really check for exisiting "combinations" (1-1-1-0001 e.g.)
and saves it as 1-1-1-0001 even if there's already one with that "id"

i am deeply sorry for the troubles...
and whenever you're in berlin, i am gonna give a beer or something :D

**bakerman2** · 10-10-2017, 02:45 PM

This should do it.

PS I know you Germans love beer (we Belgians too) but I'll stick to coffee if you don't mind.

**sqrcbc** · 10-11-2017, 06:44 AM

Have you taken a look at the edit form? and the respective "save-function"?

I seem to be unable to get it to work this way...

**bakerman2** · 10-11-2017, 07:08 AM

Works fine for me.
If I call an existing item and change Raum, Regal und Regalsegment to an already existing item, when writing to worksheet, the last four numbers are changed to the next consecutive number.

**sqrcbc** · 10-11-2017, 10:28 AM

Okay this is awkward...

well, what if you dont Change anything at all (or just the title)?
just call an item, and hit save, then evrything gets saved as 1-1-1-0006 e.g.

**bakerman2** · 10-12-2017, 08:06 PM

This should do it.

**sqrcbc** · 10-23-2017, 03:33 AM

Hey Bakerman,
at first, i am sorry for my late response, been busy with my actual job, so i couldnt quite take care of this...
yet, once again i can just say thank you, and yet once again i would have to ask, whether there's a chance i can apply a different format to the listbox.
I had quite some troubles with the "style" of the columns, so i made a "hard head" with an image file...

And somehow i am totally unable to get it working with your code ^^

Please Login or Register  to view this content.

And i just noticed, that, if i change the data from 1-1-1-0001 to for example 2-3-1-xxxx and return to 1-1-1-xxxx, it actually takes the highest 1-1-1-xxxx value and applies +1 to it...

same goes for the "new entry" one...
Did i miss anything on this in general?
Or is

Please Login or Register  to view this content.

simply not enough to perform a "check for the lowest possible value?

And if you get back to me on that offered coffee, trust me, i will make sure you get the best and tallest coffee you ever had in your whole life!

Userform VBA - fetch data from table, editable, save

LinkBack

Thread Tools

Rate This Thread

Display

Userform VBA - fetch data from table, editable, save

Re: Userform VBA - fetch data from table, editable, save

Re: Userform VBA - fetch data from table, editable, save

Re: Userform VBA - fetch data from table, editable, save

Re: Userform VBA - fetch data from table, editable, save

Re: Userform VBA - fetch data from table, editable, save

Re: Userform VBA - fetch data from table, editable, save

Re: Userform VBA - fetch data from table, editable, save

Re: Userform VBA - fetch data from table, editable, save

Re: Userform VBA - fetch data from table, editable, save

Re: Userform VBA - fetch data from table, editable, save

Re: Userform VBA - fetch data from table, editable, save

Re: Userform VBA - fetch data from table, editable, save

Re: Userform VBA - fetch data from table, editable, save

Re: Userform VBA - fetch data from table, editable, save

Re: Userform VBA - fetch data from table, editable, save

Re: Userform VBA - fetch data from table, editable, save

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Excel userform textBox pass SQl query and fetch data from close workbook

Using userform fetch data from close workbook using SQL Query

[SOLVED] Need Macro/Formula to Fetch the data from table

Fetch Data from Access table into Excelsheet

Fetch data from 1 table and post in another table in same excel sheet after pivoting

Populating VBA userform and Fetch previously entered data edit and make new entry

How to fetch the table from ASP web site and save in Excel

Bookmarks

Bookmarks

Posting Permissions