Solved

After clicking the search button on the search form, How can I get the listbox to populate with only matching criteria?

Posted on 2008-06-16
7
278 Views
Last Modified: 2013-11-28
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
0
Comment
Question by:laajsab
  • 3
  • 3
7 Comments
 
LVL 27

Assisted Solution

by:MikeToole
MikeToole earned 20 total points
Comment Utility
I presume that the listbox has a query  RowSource that references the LastName/ FirstName textboxes in a Where statement? Then, if the  listbox is called MyListBox:
Me.MyListBox.Requery
0
 
LVL 1

Accepted Solution

by:
SugarMag76 earned 80 total points
Comment Utility
Assume that no Row Source for the List Box.
Assume that the Last Name field is called txtLastName.
Assume that the First Name field is called txtFirstName.
Assume that List Box is called lstResults.

You can take the code below, paste it into your form module and then type in Call AdjustListBox into the OnClick event of the View Selection command button.
Private Sub AdjustListBox()

Dim strWhere As String
 

    If Not IsNull(Me.txtLastname) Then

        strWhere = "[Last Name] = '" & Me.txtLastname & "'"

    Else

        strWhere = ""

    End If

    

    If Not IsNull(Me.txtFirstName) Then

        If strWhere = "" Then

            strWhere = "[First Name] = '" & Me.txtFirstName & "'"

        Else

            strWhere = strWhere & " and " & "[First Name] = '" & Me.txtFirstName & ""

        End If

    End If

    

    Me.lstResults.RowSource = "SELECT [ID], [Last Name], [First Name], [Birthdate] FROM tablename WHERE strWhere"

End Sub

Open in new window

0
 

Author Comment

by:laajsab
Comment Utility
Let me rephrase what I want done.  Here is a scenario.
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!
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Expert Comment

by:SugarMag76
Comment Utility
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.
0
 

Author Comment

by:laajsab
Comment Utility
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?
0
 
LVL 1

Expert Comment

by:SugarMag76
Comment Utility
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

Open in new window

0
 

Author Closing Comment

by:laajsab
Comment Utility
Thanks to both of you for your help!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now