Search as you type with MS Access Combobox

Solution 1:

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.Dropdown
End 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

Solution 2:

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.