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

Vlado's answer is great, but if you try to use the arrow keys it clears the search results. Here's what I came up with to fix that:

Private Const RecordSQL As String = "SELECT [HH_ID], [DisplayName] FROM _HOUSEHOLDS"

Private Sub ctlSearch_Change()
'filter dropbox as you type
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset(RecordSQL & " WHERE DisplayName = '" & ctlSearch.text & "' ORDER BY [DisplayName]")
    
    If (rs.BOF And rs.EOF) Then 'only requery on no exact match
        ctlSearch.RowSource = RecordSQL & " WHERE DisplayName Like '*' & ctlSearch.text & '*' ORDER BY [DisplayName]"
        ctlSearch.Dropdown
    End If
End Sub

Private Sub cbxAddHH_AfterUpdate()
    'reset dropdown list
    ctlSearch.RowSource = RecordSQL
End Sub

Pretty sure this won't work if your combobox contains duplicate values, but I hope this helps.

Answer 2

Try this VBA code:

Public Function ComboBoxSearch(P_ComboBox As Control, P_DefaultSql As String, P_FieldSeachName As String)
    Dim V_SearchStr As String
    If Me.ActiveControl.Name = P_ComboBox.Name Then
        V_SearchStr = P_ComboBox.Text
    Else
        V_SearchStr = P_ComboBox
    End If
    If Len(V_SearchStr) Then
        Dim P_DefaultSql2 As String
        If P_DefaultSql Like "*ORDER*" Then
            P_DefaultSql2 = Mid(P_DefaultSql, InStr(1, P_DefaultSql, "ORDER"))
            P_DefaultSql = Left(P_DefaultSql, InStr(1, P_DefaultSql, "ORDER") - 1)
        End If
        P_ComboBox.RowSource = P_DefaultSql & " WHERE " & _
        P_FieldSeachName & " LIKE '*" & V_SearchStr & "*'" & P_DefaultSql2
    Else
        P_ComboBox.RowSource = P_DefaultSql
    End If
    P_ComboBox.Dropdown
    P_ComboBox.SelLength = Len(V_SearchStr)
End Function

Answer 3

I've searched far and wide and this has been the closest to what I've done, so I'd leave my script here. It's about putting multiple phrases in a textbox and having it search through a field and produce results in a listbox (like google). I work in the medical field therefore terminologies and phrases can be very complex.

Smart Man's post above is similar to what I've done, I tried his method but wasn't successful. What was challenging was the formulation of a For Loop as he did, but my version varied the WHERE clause to the SQL statement and it searches up to four phrases like google. I hard-coded for the number of expected phrases, if anyone comes up with a corrective For Loop feel free to share. I only got a For Loop to work for two phrases at a time as opposed to multiple phrases.

In other search methods I've seen, you have to type the phrases in the order they are in the table and that limits the users, especially in medicine. So here goes.....

 Private Sub txtbox_Change()

    Dim text As String
    Dim output As String
    Dim ArrayNo As Integer
    
    text = Me.txtbox.text
    
    Dim whereclause() As String
    
        whereclause = Split(text, " ")
        
        'output = Join(whereclause, "*")  'This will cause the search to be in order of the entry/array. We really want it in any order
    
        'Using a method for 4 string phrases
        ArrayNo = UBound(whereclause)
       
       If ArrayNo < 1 Then
                output = Join(whereclause(), " Table.FieldName LIKE '*") 'String that works for first array
            ElseIf ArrayNo = 1 Then
              output = whereclause(0) & "*' And Table.FieldName Like '*" & whereclause(1) & ""
            ElseIf ArrayNo = 2 Then
               output = whereclause(0) & "*' And Table.FieldName Like '*" & whereclause(1) & "*' And Table.FieldName Like '*" & whereclause(2) & ""
            ElseIf ArrayNo = 3 Then
                output = whereclause(0) & "*' And Table.FieldName Like '*" & whereclause(1) & "*' And Table.FieldName Like '*" & whereclause(2) & "*' And Table.FieldName Like '*" & whereclause(3) & ""
        End If
            
    Dim SQL As String
    
     SQL = "SELECT Table.FieldName FROM icd10_codes_combined " & _
                " WHERE Table.FieldName LIKE '*" & output & "*' "
    
        List.Visible = True
        List.RowSource = SQL
    
    End Sub

Answer 4

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 5

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 6

Try using this This is much simpler than your mentioned source.

Option Compare Database
Option Explicit
'************* Code Start **************
' This code was originally written by OpenGate Software
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
' OpenGate Software    http://www.opengatesw.net

Function fLiveSearch(ctlSearchBox As TextBox, ctlFilter As Control, _
                      strFullSQL As String, strFilteredSQL As String, Optional ctlCountLabel As Control)
'==================================================================================
'  THIS FUNCTION ALLOWS YOU TO FILTER A COMBO BOX OR LIST BOX AS THE USER TYPES
'  ALL YOU NEED TO DO IS PASS IN THE CONTROL REFERENCE TO THE SEARCH BOX ON YOUR
'  FORM, THE LISTBOX/COMBO BOX YOU WANT TO FILTER, AND WHAT THE FULL AND FILTERED
'  SQL (ROWSOURCE) SHOULD BE.
'
'  ctlSearchBox       THE TEXTBOX THE USER TYPES IN TO SEARCH
'
'  ctlFilter          THE LISTBOX OR COMBOBOX ON THE FORM YOU WANT TO FILTER
'
'  strFullSQL         THE FULL ROWSOURCE YOU WANT TO DISPLAY AS A DEFAULT IF NO
'                     RESULTS ARE RETURNED
'
'  strFilteredSQL     THE FILTERED ROWSOURCE FOR THE LISTBOX/COMBOBOX; FOR EXAMPLE
'                     YOU WOULD WANT TO USE '...like ""*" & me.txtsearch.value & "*"""
'                     TO FILTER THE RESULTS BASED ON THE USER'S SEARCH INPUT
'
' ctlCountLabel       (OPTIONAL) THE LABEL ON YOUR FORM WHERE YOU WANT TO DISPLAY THE
'                     COUNT OF ROWS DISPLAYED IN THE LISTBOX/COMBOBOX AS THEY SEARCH
'=====================================================================================

'ADVANCED PARAMETERS - Change these constants to change the behaviour of the search
  Const iSensitivity = 1 'Set to the number of characters the user must enter before the search starts
  Const blnEmptyOnNoMatch = True 'Set to true if you want nothing to appear if nothing matches their search


10    On Error GoTo err_handle

          'restore the cursor to where they left off
20        ctlSearchBox.SetFocus
30        ctlSearchBox.SelStart = Len(ctlSearchBox.Value) + 1

40        If ctlSearchBox.Value <> "" Then
                 'Only fire if they've input more than two characters (otherwise it's wasteful)
50               If Len(ctlSearchBox.Value) > iSensitivity Then
60                   ctlFilter.RowSource = strFilteredSQL
70                   If ctlFilter.ListCount > 0 Then
80                       ctlSearchBox.SetFocus
90                       ctlSearchBox.SelStart = Len(ctlSearchBox.Value) + 1
100                   Else
110                     If blnEmptyOnNoMatch = True Then
120                      ctlFilter.RowSource = ""
130                     Else
140                      ctlFilter.RowSource = strFullSQL
150                     End If
160                   End If
170             Else
180               ctlFilter.RowSource = strFullSQL
190             End If

200        Else
210           ctlFilter.RowSource = strFullSQL
220        End If

            'if there is a count label, then update it
230         If IsMissing(ctlCountLabel) = False Then
240           ctlCountLabel.Caption = "Displaying " & Format(ctlFilter.ListCount - 1, "#,##0") & " records"
250         End If

260   Exit Function
err_handle:
270   Select Case Err.Number
    Case 91 'no ctlCountLabel
       'exit
280       Case 94 'null string
       'exit
290       Case Else
300         MsgBox "An unexpected error has occurred: " & vbCrLf & Err.Description & _
            vbCrLf & "Error " & Err.Number & vbCrLf & "Line: " & Erl
310   End Select


End Function
'   ***** Code End ******

Answer 7

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.