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
282 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
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

911 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

22 Experts available now in Live!

Get 1:1 Help Now