Full Version: Populate ListBox
UtterAccess Forums > Microsoft® Access > Access Forms
brownboy
I currently have a search form. On the search form I have a text box and a list box. Both are unbounded. The user will type a name in the text box and all the people with that name will appear in the list box.
I have been able to get this working, however one problem that I am having is that everytime I open the Search form, it prompts with with Enter Parameter Value box. What i want is it to go directly to the search form and ahve the user enter the search key in the text box not in the Enter Parameter Value box that it prompts me with.
Any help with this will be much appreciated.
Jack Cowley
It sounds like the query is the Record Source for the form and if that is the case you need to use an unbound form. Now sure what your query is doing so more details will help someone here to help you...
ack
brownboy
Well what i have is a main form, where a user can look at the details of a patient (ie name age, address, city etc..) Then the user can press a search button which will bring up a pop up search form. In this form there is a text field and a list box. Intially the list box will have all the records listed. When the user types in a name such as "Frank" the list box will filter out and leave the people with the first name frank.
got the filtering to work, however when ever i press teh search button to bring up the search form, this "Enter Parameter field" keeps popping up. Is there anyway that I can have the row source for the listbox connected to the value inside of the text box?
thanks for the help!
brownboy
I did find an example on this site of a search box. I tried to do the same thing the author did but it doesnt seem to work. I dotn know how the listBox is referncing the variable "txtSearchExpression". I know it does it in the criteria field, but I dont understand how this "txtSearchExpression is given a default value.
Jack Cowley
If the Row Source of the list box is a query that gets its criteria from the text box on the search form then use code like this in the criteria field of the query:
Forms]![NameOfSearchForm]![NameOfTextBox]
Is this what you are after?
Jack
brownboy
Alright Jack!! However, I am now stuck with a new problem. lol. When I change the value in the text box, how does the List Box know to change.
This is the code i have right now for Change Event in the list box :
Private Sub TextBox_Change()
On Error GoTo Err_TextBox_Change
Me!txtSearchExpression = Me!TextBox.Text & "*"
ListBox.Requery
Exit_TextBox_Change:
Exit Sub
Err_TextBox_Change:
MsgBox Err.description
Resume Exit_TextBox_Change
End Sub
brownboy
I have to figure out how to write a query in this where the value that I am searching for is in the TextBox. I am really new to Access. So please be patient.
Thanks
Jack Cowley
I would not use a text box for data entry if you are looking for something that you know exists in the database. If, for example, you were looking for a last name then create a combo box based on the table of names. Use this combo box to find the exact record you want. If you wanted to find everyone named Brown you would start by typing B and instantly the combo box moves to the first B in the list. The more you type the closer you get to the name you want.
OK, now you have the combo box and you have your query that is the Row Source for you list box. In the After Update event of your combo box use code like this:
Me.NameOfListBox.Requery
That should do it and you should be good to go. Keep us posted on how you are doing...
Jack
brownboy
Thanks for the help Jack. However, I dont think a combo box would be ideal in my situation. Because I am goign to have another text field that will allow a user to search based on an ID number. So if they wanted to bring up a record on ID 93, then the record would show up. Also, because I am working with a DB that was created by someone else, i have foudn a number of duplicates in the DB. So a lot of records have the same ID number.
Can you think of any way that the search can be done with a text box instead of the Combo Box. I tried the:
Me.NameofListBox.Requery
And it worked sort of. However it will only display the results after i erase the value i was searching for in the text box lol. I'll try to fiddle around with it, but if you have any other suggestions please do share.
Thanks again,
Jack Cowley
Are your users going to be entering the entire string they are searching for or are they going to be entering partial data? If, for example, they want to find the string 'ill' will they enter *ill* to find Bill, Will and Still? Would they enter bi* to find Bill, Big and Bi-metal? There are lots of possibilities here so give me as much information as you can on how flexible you want your search to be and maybe what kind of data you are searching for...
ack
brownboy
The will only be able to search for the beginning of the string. So for example "Bi" will bring up Bill, Billy. And "G" will bring up Greg, Geoff, George etc...
Thanks,
Jack Cowley
I think you already have a query that is the Row Source for the list box. Use code like this in the criteria field of the column you want to search on:
Like [Forms]![NameOfForm]![NameOfTextBox] & "*"
Do not have the uses enter the asterisk, just the Bi or G or whatever.
In the After Update of the text box:
Me.NameOfListbox.Requery
Try this and see if it does what you want.
Jack
brownboy
Hi Jack,
That doesnt seem to work. Now nothing is appearing inside the ListBox. I would like the list box to intially have all the records present.
Jack Cowley
Take a look at the attached demo. It is simplified, but what I had in mind.... Will this work for you?
ack
brownboy
Do you have this working in Access 97? Thats the version I am currenlty working with. Thanks again for all your help, this is much appreciated
Jack Cowley
The attached is in Access 97....
ack
brownboy
YESS!!!, Thank you Jack. You have saved me. Your help is much appreciated.
Jack Cowley
YEEHAA! You are welcome and I'm glad the demo worked for you! Continued success with the project....
ack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.