Solved: Using a user form to search a database

[Steve41031]

Hi, I'm back one more time. I love this vba but with little information out there it makes learning it hard to understand. I have found this to be a great site and am well pleased with it.

What I have is a workbook that is simple but very useful for me. I now have it to were i can use a userform and create a data base. What I would like now is to lean how to program the same userform to search the data base and retrieve the same information that it inputed to be restored back into the userform for viewing or editing. I have read and found how to set the range as in set whatever = sheet2.range("a:a").find(what) <<< this part I am having trouble understanding. I have attached my workbook and would like an opinion or insite to help me learn what it is I am trying to do. So if anyone has anything that will help me out I would greatly appreciate it. I don't like to be given answer as I have worked many hours trying myself but I have reached that point. anyway thanks.

[lucas]

Hi Steve,
Its pretty late on a Saturday night here so I'm not able to edit your workbook but I'm going to post an address book I use that has a search and edit set up. Maybe you can get some ideas from it. If this doesn't help post back and we'll go from there.

[Steve41031]

I really appreciate it..............I can say nothing about you but good things..........Can we give you a rating.lol

[lucas]

Thanks Steve. There are lots better coders here and I have them to thank for what I offer. Most of it is a combination of the other coders here at the forum. I am going to try again to attach this file....vodka is messing with me tonight....sorry.

[Steve41031]

Whenever you get time please take a look at my workbook. I would like to enter and information in any txt box and fill the info back into the userform. I will try to digest your address book inthe mean while. I really do appreciate all your help.......again thanks. btw.............is there a way to keep the date and time from updaing in a cell when the sheet is caculated?

[lucas]

Steve, I apologize because I cannot address this tonight. Tommorrow I will look at your sheet and see what I can do. To keep the date and time from updating you will need to remove the formula's and replace them with values....I can help but not tonight. Maybe someone will come along that can help now but I will address this tommorrow if you don't get a lead tonight ok?

[lucas]

Help me out Joseph!

[Steve41031]

That's fine I'm in no hurry. Just when ever you get a chance id ok with me.............Thanks

[lucas]

Hi Steve,
I've had a chance to look your workbook over and it looks like what you have so far is the data entry form set up (not working totally yet) for registration only.....that about right?
If you have looked at the file I uploaded then you can see that there is more to this than one form....you need one to list(maybe several since you have 3 catagories)the data. That way you can search. You will in addition to your data entry form need a way(another form) to edit the selection....I don't think this can all be done in the same form. At least I don't know of a way.

[Steve41031]

is there any way you can explain to me the .find method (what,after, etc..) to me? Some of it i get and the other parts i dont.

[lucas]

This KB entry by John covers quite a bit about find:uses functions
http://vbaexpress.com/kb/getarticle.php?kb_id=821

An example from the help file:
This example finds all cells in the range A1:A500 that
contain the value 2 and changes their values to 5.

'sets the range to look in
With Worksheets(1).Range("a1:a500")

Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

[lucas]

Steve I have attached a workbook with a simple find form using textboxes. It searches col B from the first textbox and returns the value in the cell next to what your searching for in textbox 2....

There are several things you should look into when using find. If you want to find a whole exact match research xlwhole. If you just want to see if the string is anywhere in the cell look at xlpart...There are also dates, formula's, etc. Also if you wish for it to be case insensitive use Option Compare text at the top of the module outside of any subs.

Hope this helps some. Search the formum titles for find. There has been a lot posted here on the subject.

[lucas]

I hope your not going to use find to fill your texboxes. You should have a listbox to populate from the sheets that you can make a selection from. Textboxes can be below that on your form and can be populated directly from the listbox selection. Thats how the labels on the address book get populated.

Edit: I should probably qualify that post. You could probably use find to populate your textboxes, I just hadn't thought about doing it that way.

[Steve41031]

Hey I appreciate all your help. I would like for you to check the workbook out and let me knowwhat you think. Don't forget that I am still new to this. Later as I learn I will try to get it more like I want but this will do for now. any thanks so much !!

[lucas]

Looking good Steve. Search works too. Still has some bugs but you'll find that there is no end to what you can do once you get started.

If you feel like this is solved for now please mark your thread solved using the thread tools at the top of the page.

[Steve41031]

Lucas,

Hey Dude thanks for all your help, I truly appreciate it.

Steve

[lucas]

Hope I helped more than confused you on this one Steve. Let us know how it evolves. Good luck