Search as you type with MS Access Combobox

This is my Function I use to do filter combo-box as typed:

Public Sub FilterComboAsYouType(combo As ComboBox, defaultSQL As String, lookupField As String)Dim strSQL As String    If Len(combo.Text) > 0 Then        strSQL = defaultSQL & " WHERE " & lookupField & " LIKE '*" & combo.Text & "*'"    Else        strSQL = defaultSQL    'This is the default row source of combo box    End If    combo.RowSource = strSQL    combo.DropdownEnd Sub

Set the combo-box Auto Expand property to False and call the Sub FilterComboAsYouType in Change event like this:

Private Sub cmbProductName_Change()    FilterComboAsYouType Me.cmbProductName, "SELECT * FROM Product", "ProductName"End Sub

You can set up the combo or listbox something like this:

SELECT ID,Hotel,Location FROM Sometable t WHERE t.Hotel LIKE "*" & Forms!YourForm!txtSearch.Text & "*"ORDER BY t.Hotel

Then in the Change event requery the combo or listbox.

Thanks Vlado, your answer is inspiring, concise & with gif explanation. I got asked for the same functionality couple days ago from a demanding customer.

However, the request is to have a google like search. In other words, to allow including more than a phrase (ANDed ofcourse) to be searched [partially] for randomly (as you type) in the target combo. text (which was a concatenation of several fields "tags" including cocktail name, alcoholic(Y/N), ingredients, glassware, brand, category ... etc.

Now I have been asked for the same again. I thought to attach it here for future searches to help others (fellow stuck programmers). Thanks again Vlado.

here you go:

Public Sub GoogleSearch(combo As ComboBox, OriginalSQL As String, LookupField As String)' - OriginalSQL is not the recursive one  ' - Use queries to build up your sql statement, then copy/paste thier sql text in the combo row source  '   do not just point the row source to the query name (keep the query for reference if you like.'   to avoid the quotation agony of VBA built-in editor to create your OriginalSQL' - Always store your OriginalSQL in global module variable,' - Initiate it on (form) load, restore it on cancel AND after_update' Created by Walid Zohair, not to be used without the exact commentsIf Trim(combo.Text) = "" Or IsNull(combo.Text) Then    combo.RowSource = OriginalSQL    combo.Requery    combo.Dropdown    combo.SetFocus    Exit SubEnd IfDim SQLStr As StringSQLStr = Replace(OriginalSQL, ";", "") ' make sure a bar end sql is used' make sure order_by, group_by, Having will not be after where clause (gives error)' This also can be used to give clearer names in outer SQL to be used in Where clause laterSQLStr = "SELECT * FROM ( " & SQLStr & " ) WHERE "Dim StrArray() As StringStrArray = Split(Trim(combo.Text)) ' for saftey could be limited to up to 100 records only = Split(combo.text, " ", 100)For i = 0 To UBound(StrArray)    SQLStr = SQLStr & LookupField & " LIKE '*" & StrArray(i) & "*'"    If UBound(StrArray) - i > 0 Then        SQLStr = SQLStr & " AND " ' Add AND to the search string    End IfNext icombo.RowSource = SQLStrcombo.DropdownEnd Sub

Also, according to SO community guides I believe this can be considered more toward the question (better answer) rather than a reaction to other answers.