?
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
Medium Priority
?
361 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 27

Assisted Solution

by:MikeToole
MikeToole earned 80 total points
ID: 21795580
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 320 total points
ID: 21795635
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
ID: 21796152
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:SugarMag76
ID: 21796965
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
ID: 21799148
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
ID: 21799422
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
ID: 31467689
Thanks to both of you for your help!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

719 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