Using a Combo Box to search as you type

(Guest blogger, Molly Pell is a Senior Systems Analyst at FMS, Inc., a leading Access applications company, which designs custom solutions to improve operations for small and large organizations in all sectors.)

Here’s a neat trick that you can use to filter a Continuous or Split form while your users are typing in a Combo Box. As the user types, the form filter updates to display full or partial matches for the value entered.

This example uses a slightly modified version of the Northwind 2007 Template, available for download from Microsoft. The “Customer List” form is a Split form that displays a list of all customers. We want to allow users to filter this list to easily find a customer, even without knowing the full customer name.

To do this, we add a Combo Box named cboFilter, with the following notable properties:

Here’s what our form looks like:

Then in the Change event of the Combo Box, we add the following code:

Private Sub cboFilter_Change()

  ‘ If the combo box is cleared, clear the form filter.
  If Nz(Me.cboFilter.Text) = “” Then
Me.Form.Filter = “”
Me.FilterOn = False

  ‘ If a combo box item is selected, filter for an exact match.
‘ Use the ListIndex property to check if the value is an item in the list.
ElseIf Me.cboFilter.ListIndex <> -1 Then
Me.Form.Filter = “[Company] = ‘” & _
Replace(Me.cboFilter.Text, “‘”, “””) & “‘”
Me.FilterOn = True

  ‘ If a partial value is typed, filter for a partial company name match.
Else
Me.Form.Filter = “[Company] Like ‘*” & _
Replace(Me.cboFilter.Text, “‘”, “””) & “*'”
Me.FilterOn = True

End If

  ‘ Move the cursor to the end of the combo box.
Me.cboFilter.SetFocus
Me.cboFilter.SelStart = Len(Me.cboFilter.Text)

End Sub

Now as the user types, the customer list automatically updates. If the user types an exact match, the list is filtered for an exact match. If the user types a partial match, the list is filtered for any company whose name contains the string. If the user clears the combo box, the filter is cleared.

Note that this same thing can be done with a text box, but using a combo box adds the extra perk of making all values available in the drop down.

For more combo box tips, check out our Top 5 Combo Box Tips for Access at FMS, Inc.