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]

If the ListBoxClient has the a bound column which is the same as the value that is typed into txtboxClientFind, then your code would be:

  Me.ListBoxClient = Me.txtboxClientFind

Duane
Hook'D on Access
MS Access MVP

 

[rew2009]

That is still not working with the control button code. I changed back to finding a zip code rather than a client name and changed the names to: TextBoxZipCodeFind, ZipCodeFindButton, ListBoxZipCode. Neither the text box nor the list box is bound.
____________________________

The row source for the list box is:

SELECT tblClients.ID, tblClients.ZipCode, tblClients.Address, tblClients.ClientName FROM tblClients ORDER BY tblClients.ZipCode DESC;
____________________________

The code for the ZipCodeFindButton is:

Private Sub ZipCodeFindButton_Click()
On Error GoTo Err_ZipCodeFindButton_Click

Screen.PreviousControl.SetFocus
' DoCmd.FindNext

Me.ListBoxZipCode = Me.TextBoxZipCodeFind

Exit_ZipCodeFindButton_Click:
Exit Sub

Err_ZipCodeFindButton_Click:
MsgBox Err.Description
Resume Exit_ZipCodeFindButton_Click

End Sub

Thanks for the help, Russ

 

[dhookom]

What is the bound column of your list box? From you code, it appears to be the 2nd column (ZipCode). There doesn't appear to be anything in your code that sets the filter of the form or find anything on the form.

Duane
Hook'D on Access
MS Access MVP

 

[rew2009]

I may be have doing this all wrong because as a beginner in Access I created the forms and placed in the objects through design view and somehow got everything to work and have used the application for over a year now. (Just not as well as I would like). On my normal applications the forms that I use have the record source connected or controlled by a Query that orders by client name or zip code and may have a filter by geographic region. But the forms themselves are blank in the ‘form property” dialogue box filter and order lines.

In this sample application that I am currently experimenting with, I just have the “record source” for the form being the client table, but I bind the text boxes in the form that I use to view and modify data to the fields of that client table.

When I created the list box, the first question that the wizard asked was “how do you want your list box to get its values” and I clicked “Find a record on my form based on the value I selected in my list box”. Subsequently when would click a row in the list box the record shown in bound text boxes in the form would match that of the list box row selected. I think it is done through the “AfterUpdate” code below:

The list box is unbound but this is code created by the wizard for AfterUpdate()

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

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

End Sub0

Again, all I am looking for is a code to add to command button that will allow me to find a record in the table easily through the list box. The code for the command button is in the previous post.

Thanks, Russ

 

[rew2009]

Dhookom, I think I found a solution. I just found some code on the net that searches a column in a list box until it matches with an input string. I modified it to fit my application and tried it in my form and it seems to work. If there is no simpler solution, this may work for other people.

If you know of a simple one or two line solution I would love to see that too

Again, I thank you for your help and maybe this can help someone else.

Russ

 

[rew2009]

Dhookom, I think I found a solution. I just found some code on the net that searches a column in a list box until it matches with an input string. I modified it to fit my application and tried it in my form and it seems to work. If there is no simpler solution, this may work for other people.

___________________

Dim strSearch As String
Dim i As Long
Dim iContinueSearch As Integer

strSearch = InputBox("Enter Search String")
strSearch = "*" & strSearch & "*"

With Me.ListBoxZipCode
If .ListCount > 0 Then
.Selected(0) = True
For i = 0 To .ListCount - 1
If .Column(3, i) Like strSearch Then
.Selected(i) = True
iContinueSearch = MsgBox("Do you want to continue searching?", vbQuestion + vbYesNo)
If iContinueSearch = vbNo Then
Exit For
End If
End If
Next i
End If
End With
________________

If you know of a simple one or two line solution I would love to see that too

Again, I thank you for your help and maybe this can help someone else.

Russ

 

[dhookom]

This seems to be one of those threads where the end seems to be nothing like the beginning. Did your requirements/question really change that much or is it just me?

Duane
Hook'D on Access
MS Access MVP

 

[rew2009]

dhookom, It was more that I have a lot of questions that have been bugging me for a long time about this application. I should have started a new thread when I changed to looking for "Find" routine for the list box. But we solved all of these problems and answered the questions and the suggestions you made helped me understand better the vb code and how it relates to Access. I had been trying to add a “find” and a “filter” routine to my application for over a year now with no luck because it seemed that I needed to learn vb from scratch which I didn’t have the time for. I had a good working knowledge of dBase3 and Visual dBase but making the transition to vb and access made me feel like a rank beginner. I am still a beginner but I am starting to get a feel for the code.

This has been the best site for help with Access that I have found. Your response is fast and with your help I not only have the problem solved in only a few days but have much better understanding of the code and have been able to modify it to solve other problems. Again I really appreciated your help. I do have several more questions and problems with regards to other areas of my application which I would like to solve but I will start new threads. Thanks again. Russ

 

[dhookom]

Russ, thanks for the comments. I also developed in dBaseIII and survived the move to Access. I think our age is showing :-(

Duane
Hook'D on Access
MS Access MVP