Filter Listbox By Textbox Contents (1 Viewer)

[3link]

So basically I want to type something into a textbox and have that automatically run a query in my listbox for like values.

This is the code I'm using.
txtProperty = Text box
lstSearchResults = List Box
Table = Table

Private Sub txtProperty_Change()
    On Error GoTo Err_txtProperty_Change
    Dim strSource As String
    strSource = "SELECT ID, List " & _
        "FROM Table " & _
        "Where ID Like '*" & Me.txtProperty.Text & "*' " _
    Me.lstSearchResults.RowSource = strSource

Exit_txtProperty_Change:
    Exit Sub
Err_txtProperty_Change:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_txtProperty_Change
End Sub

This code doesn't work. Whenever I try, nothing happens. I remember I've done this in the past and there's a trick to it, but I don't recall what that trick was. Anyone have any ideas what I'm missing?

 

[pbaldy]

Looks okay at a glance. Is ID really the field that would contain text values? See if this helps:

http://www.baldyweb.com/ImmediateWindow.htm

 

[JHB]

I think you need to requery the listbox after you set the rowsource.
But there are also some inconveniences in the code.

• "Table" is a keyword in MS-Access and shouldn't be used as a name!
• Why do you've "Text" in "Me.txtProperty.Text", why not just "Me.txtProperty"
• Either you've made a typo or you've left some code lines out, then you've a "_" at the end of ""Where ID Like '*" & Me.txtProperty.Text & "*' " _ " and that will give you a compiler error.
• "ID" is normally used for numbers so as pbaldy mention are you sure it contains text values?

Private Sub txtProperty_Change()
    On Error GoTo Err_txtProperty_Change
    Dim strSource As String
    strSource = "SELECT ID, List " & _
        "FROM Table " & _
        "Where ID Like '*" & Me.txtProperty & "*' "
    Me.lstSearchResults.RowSource = strSource
    Me.lstSearchResults.Requery
  
Exit_txtProperty_Change:
    Exit Sub
Err_txtProperty_Change:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_txtProperty_Change
End Sub

If ID is number, then:

Private Sub txtProperty_Change()
    On Error GoTo Err_txtProperty_Change
    Dim strSource As String
    strSource = "SELECT ID, List " & _
        "FROM Table " & _
        "Where ID Like *" & Me.txtProperty & "* "
    Me.lstSearchResults.RowSource = strSource
    Me.lstSearchResults.Requery
  
Exit_txtProperty_Change:
    Exit Sub
Err_txtProperty_Change:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_txtProperty_Change
End Sub

 

[pbaldy]

I'll get out of the way. I've never needed to requery after setting a row source, and the need for the .Text property is apparent from the event.