laajsab
asked on
After clicking the search button on the search form, How can I get the listbox to populate with only matching criteria?
In my Microsoft Access database, I have a search form with the following specifications (also see attached snapshot):
At the top - I have two search fields (last name and first name). If user enters data in both, will search for both fields; otherwise, it will only search for the field that data is entered.
In the middle - I have a listbox, which will display matching search results - showing last name, first name and birthdate (id is hidden) after the Search button is clicked.
In the last section, I have 4 command buttons - search , view selection, add client, exit. When user makes a selection and click View Selection, it will take the user to a different form showing detail information. Thanks!
search-results-listbox.gif
At the top - I have two search fields (last name and first name). If user enters data in both, will search for both fields; otherwise, it will only search for the field that data is entered.
In the middle - I have a listbox, which will display matching search results - showing last name, first name and birthdate (id is hidden) after the Search button is clicked.
In the last section, I have 4 command buttons - search , view selection, add client, exit. When user makes a selection and click View Selection, it will take the user to a different form showing detail information. Thanks!
search-results-listbox.gif
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Sorry...I referred to the wrong command button in my earlier response...
You can take the code I provided earlier, paste it into your form module and then type in Call AdjustListBox into the OnClick event of the Search command button (not View Selection). This will filter the List Box according to your search parameters (i.e. Last Name and First Name). That should take care of steps 2-4. As far as step 1, just create a command button on your first form (you can use the wizard) that will open up your search form when clicked.
You can take the code I provided earlier, paste it into your form module and then type in Call AdjustListBox into the OnClick event of the Search command button (not View Selection). This will filter the List Box according to your search parameters (i.e. Last Name and First Name). That should take care of steps 2-4. As far as step 1, just create a command button on your first form (you can use the wizard) that will open up your search form when clicked.
ASKER
SugarMaq76,
After some minor modifications, it appear to be working. In case nothing is found, how can I tell the user that nothing is found? Is there a way to count the result of an sql statement?
After some minor modifications, it appear to be working. In case nothing is found, how can I tell the user that nothing is found? Is there a way to count the result of an sql statement?
Using the ListCount property of lstResults will give you the record count:
If Me.lstResults.ListCount = 0 Then
MsgBox "No records found", vbOkOnly
End If
ASKER
Thanks to both of you for your help!
ASKER
1. From the main menu, a user clicks Search Client and then this search form (see attached screen) opens up with blanks.
2. user enters data into one or both of the dilalog boxes
3. user clicks the Search button
4. search form reloads with matching results
5. user selects one of the names on the result listbox
6. when user clicks View Selection, it will sent to a detail information screen
As it is now, the search form is preloaded with all the names on the table. What I want is steps 1 through 4. I had step 5, 6 working.
Thanks for your attempts!