Dynamically update the contents of a listbox based on similarity

[iojbr]

Hi:

I have a combobox (combo0) and a listbox (list2) on a form. I want the listbox to pull up records in a table based on the combobox selection or what the user types in. But I would like to set it up so all records similar to the Combo0 selection or what the user types is displayed in the list2. Also, if the user choses to enter the text into Combo0 instead of selecting it, I would like to have the list box dynamically requery after each typed character. I tried using the "like" operator in the select statement of the list2 rowsource property and a Me.List2.requery command in the On Change event of Combo0, but I can't get it to work. Any help would be greatly appreciated.

 

[greg85374]

Ive done this many times...Use the onupdate evenet of the combobox

In the onupdate peform the following
'clear evrything out of the listbox
list2.value = ""
List2.rowsourcetype = "Value List"
List2.Rowsource = ""

List2.Rowsource = "your Sql Query"
List2.Requery 'not really needed but sometimes your just better off doing it

 

[iojbr]

Thanks for the reply.

I tried the code in the On Change event of my combobox and received a type mismatch error. I think it's because the record source for my listbox is based on a Table/Query instead of a value list. I couldn't find an OnUpdate event in the properties window. Thanks again.

 

[PHV]

In the Change event procedure of the combo build dynamically the RowSource property of the ListBox using in the WHERE clause the Text property of the combo surrounded by single quotes, something like this:
List2.RowSource = "SELECT .... WHERE yourFieldName Like '" & combo0.Text & "*'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244