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
359 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 20 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 80 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Independent Software Vendors: 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!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

617 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