Filter List box using text box

[oakoen]

I have been googling this and have no luck getting anything to work.

I have a list box (customer_name). When a name is selected the form reflects customers data. I would like to have a text box that users can type in and as they type the listbox filters upon what they have typed.

Why am I having such a hard time with this?

[pbaldy]

It's doable, but have you considered a combo instead of a list box? By default it will jump to the appropriate location in the list as the user types.

[oakoen]

Just found this and got it working.

Private Sub filterby_Change()


Dim sql As String

'this will match any entry in the list that begins with what the user has typed in the FilterBy control
sql = "SELECT customer_ID, customer_Name FROM Customer WHERE customer_Name Like '" & Me.filterby.Text & "*' ORDER BY customer_name"

'if you want to match any part of the string then add wildcard (*) before the FilterBy.Text, too:
'sql = "SELECT ColorID, ColorName FROM Colors WHERE ColorName Like '*" & Me.FilterBy.Text & "*' ORDER BY ColorName"

Me.List51.RowSource = sql

End Sub

[pbaldy]

Like I said, it's doable. From a user perspective, I believe the combo is easier. With this, I have to type in the textbox and then switch to the mouse to select from the listbox. With the combo, I can keep my hands on the keyboard (you can tell I work with data entry people a lot).