Text box to filter listbox

[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

 

[Marshall Barton]

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

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

 

[John Vinson]

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.

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

 

[John A.]

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