Search as you type with MS Access Combobox

Question

I would like to create a simple search-as-you-type combobox in Microsoft Access as shown in the image below.

NB: the above image is from a complicated implementation of what I am trying to achieve from here

My combobox is named ctlSearch. Using Visual Basic, I would like to hook on to the onChange event, detecting the user input and consequently refining the list of possible results. Is it possible to take this approach to implement the search-as-you-type combobox?

Answer 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

Answer 2

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 comments

If Trim(combo.Text) = "" Or IsNull(combo.Text) Then
    combo.RowSource = OriginalSQL
    combo.Requery
    combo.Dropdown
    combo.SetFocus
    Exit Sub
End If

Dim SQLStr As String
SQLStr = 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 later
SQLStr = "SELECT * FROM ( " & SQLStr & " ) WHERE "

Dim StrArray() As String
StrArray = 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 If
Next i
combo.RowSource = SQLStr
combo.Dropdown
End 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.

Answer 3

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.