How to refill combobox with similar records based on what user types

Question

I'm currently building a form where a user can look up a tool based on the description or part number.

I want user to be able to type any letters into the combobox that I have tied to a query listing all my tools and the combobox will repopulate itself with the tools most similar to what is present in their combobox. For example, if they start typing wre, then tools that have similar characters will start appearing in the combobox such as wrench, torque wrench, power wrench, etc.

I've tried looking around for other people's solutions to this but either I didn't fully comprehend the existing solution (I'm fairly new to Access) or it wasn't what I was looking for. I've seen that people suggested using a listbox instead but I really don't want to go down that route.

I was thinking about using what the user types in the combobox and my VBA code will pick up the "change event" and requery the combobox on the fly by using their input as the like criteria for the new query.

Is this a possible route? Will it be slower? Is there a better route?

I'm hoping someone can show some examples on how to achieve what I'm looking for.

Answer 1

The search as you type feature is very useful! With a textbox and a listbox, you can setup a dynamic search tool that will filter a list for approximate matches as you type. The textbox has four events associated with it, as seen here.

The code behind the form looks like this. Pay attention to the part in bold. This is where we create a string of SQL commands, and utilize the SQL Like operator, to get dynamic matches as we type. Pay attention to the text in bold below.

Option Compare Database
Option Explicit On

Private blnSpace As Boolean  'INCLUDE THIS LINE ON YOUR FORM

Private Sub btnClearFilter_Click()
    'CODE FOR THE RED "X" BUTTON TO CLEAR THE FILTER AND SHOW ALL
    On Error Resume Next
    Me.txtSearch.Value = ""
    txtSearch_Change()
End Sub

Private Sub txtSearch_Change()
    'CODE THAT HANDLES WHAT HAPPENS WHEN THE USER TYPES IN THE SEARCH BOX
    Dim strFullList As String
    Dim strFilteredList As String


    If blnSpace = False Then
        Me.Refresh 'refresh to make sure the text box changes are actually available to use

        'specify the default/full rowsource for the control
        strFullList = "SELECT RecordID, First, Last FROM tblNames ORDER BY First;"

        'specify the way you want the rowsource to be filtered based on the user's entry
        strFilteredList = "SELECT RecordID, First, Last FROM tblNames WHERE [First] LIKE ""*" & Me.txtSearch.Value &
                          "*"" OR [Last] LIKE ""*" & Me.txtSearch.Value & "*"" ORDER BY [First]"

        'run the search
        fLiveSearch Me.txtSearch, Me.lstItems, strFullList, strFilteredList, Me.txtCount
    End If

End Sub

Private Sub txtSearch_KeyPress(KeyAscii As Integer)
    'NECESSARY TO IDENTIFY IF THE USER IS HITTING THE SPACEBAR
    'IN WHICH CASE WE WANT TO IGNORE THE INPUT

    On Error GoTo err_handle

    If KeyAscii = 32 Then
        blnSpace = True
    Else
        blnSpace = False
    End If


    Exit Sub
err_handle:
    Select Case Err.Number
        Case Else
            MsgBox "An unexpected error has occurred: " & vbCrLf & Err.Description &
                  vbCrLf & "Error " & Err.Number & "(" & Erl() & ")"
   End Select
End Sub
Private Sub txtSearch_GotFocus()
    ' USED TO REMOVE THE PROMPT IF THE CONTROL GETS FOCUS
    On Error Resume Next
    If Me.txtSearch.Value = "(type to search)" Then
        Me.txtSearch.Value = ""
    End If
End Sub
Private Sub txtSearch_LostFocus()
    ' USED TO ADD THE PROMPT BACK IN IF THE CONTROL LOSES FOCUS
    On Error Resume Next
    If Me.txtSearch.Value = "" Then
        Me.txtSearch.Value = "(type to search)"
    End If

End Sub

Finally, in a regular module, you will need this script.

Option Compare Database
Option Explicit On

'************* 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)
    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


    On Error GoTo err_handle

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

    If ctlSearchBox.Value <> "" Then
        'Only fire if they've input more than two characters (otherwise it's wasteful)
        If Len(ctlSearchBox.Value) > iSensitivity Then
            ctlFilter.RowSource = strFilteredSQL
            If ctlFilter.ListCount > 0 Then
                ctlSearchBox.SetFocus
                ctlSearchBox.SelStart = Len(ctlSearchBox.Value) + 1
            Else
                If blnEmptyOnNoMatch = True Then
                    ctlFilter.RowSource = ""
                Else
                    ctlFilter.RowSource = strFullSQL
                End If
            End If
        Else
            ctlFilter.RowSource = strFullSQL
        End If

    Else
        ctlFilter.RowSource = strFullSQL
    End If

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

    Exit Function
err_handle:
    Select Case Err.Number
        Case 91 'no ctlCountLabel
       'exit
        Case 94 'null string
            'exit
        Case Else
            MsgBox "An unexpected error has occurred: " & vbCrLf & Err.Description &
               vbCrLf & "Error " & Err.Number & vbCrLf & "Line: " & Erl()
   End Select

End Function

The code comes from this link:

http://www.opengatesw.net/ms-access-tutorials/Access-Articles/Search-As-You-Type-Access.html