Search within a ComboBox

Private Sub Ultimate_ID_Change()

' Function Description:
' Filter a combo box list as the user types.
' This is accomplished by grabbing the text typed by the user in the
' combo box's edit field, creating an SQL SELECT statement from it,
' and finally applying that SQL statement to the combo box's
' .RowSource property.

Dim strText, strFind
' Get the text that the user has typed into the combo box editable field.
strText = Me.[Ultimate ID].Text
' If the user has typed something in, then filter the combobox
' list to limit the visible records to those that contain the
' typed letters.
' Otherwise (if the field is blank), the user has deleted whatever
' text they typed, so show the entire (unfiltered) list
If Len(Trim(strText)) > 0 Then
    ' Show the list with only those items containing the typed
    ' letters.
    ' Create an SQL query string for the WHERE clause of the SQL
    ' SELECT statement.
    strFind = "tbl_ultimate.[Ultimate Parent Name] Like '"
    For i = 1 To Len(Trim(strText))
        If (Right(strFind, 1) = "*") Then
            ' When adding another character, remove the
            ' previous "*," otherwise you end up with
            ' "*g**w*" instead of "*g*w*."
            ' This has no apparent impact on the user, but
            ' ensures that the SQL looks as intended.
            strFind = Left(strFind, Len(strFind) - 1)
        End If
        strFind = strFind & "*" & Mid(strText, i, 1) & "*"
    Next
    strFind = strFind & "'"
    
    ' Create the full SQL SELECt string for the combo box's
    ' .RowSource property.
    
    strSQL = "SELECT tbl_ultimate.[Ultimate ID], tbl_ultimate.[Ultimate Parent Name] FROM tbl_ultimate Where " & _
    strFind & " ORDER by tbl_ultimate.[Ultimate Parent Name] ;"
    
    '' NOTE: to remove the order requirement, such that typing "wg"
    '' and "gw" return the same results, the SQL WHERE clause needs
    '' to look like "Name Like '*w* AND *g*'."
    '' The code above should be changed as follows:
    ''For i = 1 To Len(Trim(strText))
    ''   strFind = strFind & "Name Like '*" & Mid(strText, i, 1) & "*' And "
    ''Next
    ''
    ''strSQL = "SELECT tName.nameKey, tName.Name, SortOrder from tblApps Where " & _
    ''Left(strFind, Len(strFind) - 5) & " Order By SortOrder"
    
    ' Filter the combo list records using the new SQL statement.
    Me.[Ultimate ID].RowSource = strSQL
    
Else
    ' Show the entire list.
    
    strSQL = "SELECT tbl_ultimate.[Ultimate ID], tbl_ultimate.[Ultimate Parent Name] FROM tbl_ultimate ORDER BY tbl_ultimate.[Ultimate Parent Name]; "
    Me.[Ultimate ID].RowSource = strSQL
End If

' Make sure the combobox is open so the user
' can see the items available on list.
Me.[Ultimate ID].Dropdown

End Sub

Select allOpen in new window