Text box to filter listbox

  • Thread starter Thread starter John
  • Start date Start date
J

John

I'm stumped. What I'd like to do is filter the contents of a list box as a
user types in a text box, keypress by keypress. Something like the way the
AutoExpand property works in a combobox, except that I'd like the listbox to
show only matching records.

Example:
Textbox:
Listbox: AAAA,AABA,ABBA,BACD,CAAA (from a table or recordset)

User types "A" in textbox,
Textbox: A
Listbox: AAAA,AABA,ABBA

User types a second "A" in textbox
Textbox: AA
Listbox: AAAA,AABA

User types a "B" in textbox
Textbox: AAB
Listbox: AABA

Any ideas greatly appreciated,
John
 
John said:
I'm stumped. What I'd like to do is filter the contents of a list box as a
user types in a text box, keypress by keypress. Something like the way the
AutoExpand property works in a combobox, except that I'd like the listbox to
show only matching records.

Example:
Textbox:
Listbox: AAAA,AABA,ABBA,BACD,CAAA (from a table or recordset)

User types "A" in textbox,
Textbox: A
Listbox: AAAA,AABA,ABBA

User types a second "A" in textbox
Textbox: AA
Listbox: AAAA,AABA

User types a "B" in textbox
Textbox: AAB
Listbox: AABA
Click to expand...


Set the list box's RowSource to an SQL statement like this:

SELECT fld FROM table WHERE fld Like
Forms!theform.textbox.Text & "*" ORDER BY fld

Use the text box's Change event to sync with the list box:

Me.listbox.Requery
 
I'm stumped. What I'd like to do is filter the contents of a list box as a
user types in a text box, keypress by keypress. Something like the way the
AutoExpand property works in a combobox, except that I'd like the listbox to
show only matching records.
Click to expand...

Use the CHANGE event of the textbox, which fires at every keystroke,
to change the listbox's RowSource query:

Private Sub txtFilter_Change()
Dim strSQL As String
strSQL = "SELECT fieldname FROM table WHERE fieldname = "
strSQL = strSQL & "'" & Me!txtFilter & "*'"
strSQL = strSQL & " ORDER BY fieldname;"
Me!lstFieldname.RowSource = strSQL
End Sub
 
Thanks Marsh and John,
I had tried all of Marsh's idea except the ".text" property of the textbox -
that made it work just as I wanted.

Thanks again,
John
 
Back
Top