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
356 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

739 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