Filter the display of a list box with a user input in an Access Form 1

[rew2009]

I am a technical user and have created an access front end for a database with several thousand local client addresses in my city. I display them in an Access Form through a list box. I want to be able to filter on zip codes by adding a separate user input text box and a command button which would activate the filter and only show those clients with that zip code. Then additional code controlled by a second button that will turn the filter off. My background has been with Visual-dBase. I am fairly new to Access and I have limited experience with VB but learning. Thanks

 

[dhookom]

I wouldl use code in the command button to change the Row Source of the combo box. The code might look like:

   Dim strSQL as String
   If Not IsNull(Me.txtZipCode) Then
      strSQL = "SELECT ClientID, ClientName FROM tblClients " & _
         "WHERE ZipCode ='" & Me.txtZipCode & "'" & _
         " ORDER BY ClientName"
      Me.cboClient.RowSource = strSQL
   End If

Code to undo the row source would be similar.

Duane
Hook'D on Access
MS Access MVP

 

[rew2009]

Thank you for your assistance. I am using a listbox rather than a combobox. How would the code be different? Also I follow the general thrust of the code you included, but I am still unfamiliar with some of the specific syntax. Could you recommend a good online resource(s) to bring me up to speed quickly and are there any web sites that I could down load and study simple samples of forms with listboxes and command buttons controls such as I am using. Again I really appreciate your help.

 

[dhookom]

The code would be the same for a list box other than your naming convention for the controls.

Consider looking through the samples at Roger Carlsons site. Roger has a ton of small sample databases include at least one for cascading combo boxes. This would be very similar to your situation. You might even consider converting your zipcode text box to a combo box of unique zipcodes.

Duane
Hook'D on Access
MS Access MVP

 

[rew2009]

Thanks, dhookom, I will try out the code and visit the Roger Carlson site.

 

[rew2009]

dhookom, thanks, the code worked and displayed the filtered records correctly. I added a copied version of your code modified so that I it would remove the filter upon seeing a blank zip code, and that worked!

However, after initially opening the form, but before clicking on the zipcode filter, the list box would normally allow me to click on a record in the list and the form would go to that record and other text boxes in the form connected to fields in that record would display the selected record info correctly.

However, after activating the zip code filter with the command button and the new code, the list box correctly shows the filtered zipcode records but appears to be disconnected from the the form, i.e., if a filtered record is clicked in the list box, the form text boxes do not change to show the new record info.

This is your slightly modified code as I am now using it:

Dim strSQL As String
If Not IsNull(Me.Text22) Then
strSQL = "SELECT ClientID, ClientName,ZipCode FROM tblClients " & _
"WHERE ZipCode ='" & Me.Text22 & "'" & _
" ORDER BY ClientName"
Me.List20.RowSource = strSQL
End If

'New part to clear the zipcode filter

If IsNull(Me.Text22) Then
strSQL = "SELECT ClientID, ClientName,ZipCode FROM tblClients " & _
" ORDER BY ZipCode "

Me.List20.RowSource = strSQL

End If

Also, I need the "ORDER BY ZipCode" above to be "Descending", but I couldn't figure out how to add it in. I tried several combinations but to no avail. I am sure it is very simple.

Again, Thanks,

 

[dhookom]

We don't know how/why the selected value in the list box was supposed to filter or move to a record in the form.

To get the zipcodes in descending order, try:

 If IsNull(Me.Text22) Then
      strSQL = "SELECT ClientID, ClientName,ZipCode FROM tblClients " & _
         " ORDER BY ZipCode Desc"
      Me.List20.RowSource = strSQL

   End If

Also, you should consider using a naming convention. "Text22" should have been renamed something like "txtZipCodeFilter".

Duane
Hook'D on Access
MS Access MVP

 

[rew2009]

Dhookom, Thanks, That “Desc” placement worked and I will take your advice on the naming convention. When you are not a professional programmer you can sometimes think that there is something magic with the names that the Wizards programs give to the various objects when it is first creating a form – but that is obviously not the case. Good advice to use recognizable names.

The first page of the form shows the pertinent information in bound text boxes from the fields of a selected record in the database table and the purpose is to view the info in the fields in a better interface and allow modification by the user through the text boxes. The record is selected from a list box either on that same first page or on another tabbed page. When I clicked on a record in the list box located on the same form page as the above text boxes, the info in those text boxes change to reflect the info of the fields in the selected record of the list box. The selected list box record is connected to the adjacent text boxes. However, after applying the zip code filter that you helped me with, the correct filtered records are displayed in the list box, but when one of the records is selected (clicked) it does not cause the adjacent text boxes on the form to change the displayed information to that of the selected record. It is like the list box becomes disconnected from those text boxes when the filter is applied. How do I correct this.

Also, This is my first time on this site and I was going to upload a screen shot of the of some of my forms so that you could see the application interface but I could not figure out how to do that on this site. It looks like the attachment feature is just for web site addresses. I thought that could make my questions a little clearer. Again, I appreciate the help.

 

[dhookom]

Selecting a value in a list box doesn't automatically navigate to a particular record in a form unless there is code that moves to the record or applies a filter. I would probably add code to the after update of the final selection to set a filter to the primary key of the record source of the form.

Duane
Hook'D on Access
MS Access MVP

 

[rew2009]

That sounds good but what would the code look like? This is what currently is in "List20_AfterUpdate()"

Private Sub List20_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![List20], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks

 

[dhookom]

I'm not sure why you need the Str(Nz(... stuff. Have you tried placing a breakpoint in the code at the "Set rs =" line to step through the code? I would probably use code like:

   Me.Filter = "[ID]=" & Me!List20
   Me.FilterOn = True

Duane
Hook'D on Access
MS Access MVP

 

[rew2009]

Dhookom, The test or sample access application that I am working on is short and simple (one page form) with an abbreviated table to test these concepts before I put it in the actual application. Is it possible to upload this sample application to you so that you could take a look at it. I am sure the solution is very simple.

That ‘Str(Nz(... stuff” is what the 'Form Wizard' created. I understand only part of it as my familiarity is with Visual dBase.

I tried your suggestion in place of the code in Private Sub List20_AfterUpdate() - i.e.:

Me.Filter = "[ID]=" & Me!List20
Me.FilterOn = True

But it didn't solve the problem.

Thanks, Russ

 

[dhookom]

Russ,
Have you tried setting a breakpoint in the code? Is the ID field text or numeric?

What is the record source of the form?

Duane
Hook'D on Access
MS Access MVP

 

[rew2009]

Dhookom, I figured this out. I changed the code to start the "select" with "select ID" as shown below and it worked. I am not sure why, possibly because ID is the first text box in the form but the new code is:

If Not IsNull(Me.txtZipCodeFilter) Then
strSQL = "SELECT ID, ClientID, ClientName,ZipCode FROM tblClients " & _
"WHERE ZipCode ='" & Me.txtZipCodeFilter & "'" & _
" ORDER BY ClientName"
Me.List20.RowSource = strSQL
End If
If IsNull(Me.txtZipCodeFilter) Then
strSQL = "SELECT ID, ClientID, ClientName,ZipCode FROM tblClients " & _
" ORDER BY ZipCode Desc "

Me.List20.RowSource = strSQL

Again, I appreaciated your help and I will have other questions to take care of some other loose ends in the code.

Thanks Russ

 

[rew2009]

Dhookom, The Zip Code Filter is working fine, and I will transfer the code to do the same with other fields that I need to filter on. I noticed that the code only allows me to filter on the complete zip code; i.e., it works if I put "55435" but if I put "5543" it doesn't work with the wider filtering. How would I modify the code solve this problem? Below is what I am using now with the filter button:

Dim strSQL As String
If Not IsNull(Me.txtZipCodeFilter) Then
strSQL = "SELECT ID, ClientID, ClientName,ZipCode FROM tblClients " & _
"WHERE ZipCode ='" & Me.txtZipCodeFilter & "'" & _
" ORDER BY ClientName"
Me.ListBoxZipCode.RowSource = strSQL
End If
If IsNull(Me.txtZipCodeFilter) Then
strSQL = "SELECT ID, ClientID, ClientName,ZipCode FROM tblClients " & _
" ORDER BY ZipCode Desc "

Me.ListBoxZipCode.RowSource = strSQL
End If

Thanks, Russ

 

[PHV]

"WHERE ZipCode LIKE '" & Me.txtZipCodeFilter & "*'" & _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rew2009]

That worked, Thank you Russ

 

[rew2009]

One other question, how would I modify the code just to have the list box go to the first record with the zip code matching the txt box entry (The list box would be in zip code order)without filtering out the others?

Thanks Russ

 

[dhookom]

To set a list box to its first value, the code would be

    Me.ListBoxZipCode = Me.ListBoxZipCode.ItemData(0)

Duane
Hook'D on Access
MS Access MVP

 

[rew2009]

I apologize, I wasn’t clear on my question. I have a different list box that I am not filtering. It is displaying all of the records in the table ordered or sorted to a specific field such as client name (or zip code), etc. In my actual table I have over 12,000 records and one of the list boxes is ordered by client name. If I want to go to a particular client name, I currently I have to use the slide bar on the side of the list box to wade through maybe half or more of the 12000 records to find that client name. What I want is a “Find” routine that will allow me to use a text box to enter the clients name and click a command button (different from the zipcode filter button) and have a ‘find’ code take the display in the list box to that client record entered into the txt box. Again, I’m sure it’s pretty simple, but I find I am learning this code far more quickly when I ask an expert than when I try to wade through the Microsoft Help menus which only half the time point me in the right direction and even when it does, the next challenge is getting the syntax exactly correct - very frustrating.

Again, assume the text box is named ‘txtboxClientFind’, the command button is ‘ClientFindButton’, and the list box is ‘ListboxClient’. What would the code connected to the command button be to produce this “Find” routine? Again, I appreciate the help.

Thanks, Russ