How-to: Filter as You type.

Instantly filter the records displayed on a continuous form.

Below is a function that you call by passing the name of a Field on which you want to filter and the name of a Textbox on the current form in which you will type the search string. The function takes a value from the text control, surrounds it with wildcard characters '*' and then filters the current form.

Sub FilterFunc(strField As String, strControl As String)
'On Error GoTo Err_Handler

Dim strText As String       'The text to search for.
Dim lngSelStart As Long     'Selection Starting point.

strText = Me(strControl).Text

' at this point you may want to remove any problematic characters like quotation marks from strText with replace

lngSelStart = Me(strControl).SelStart

'Save any uncommitted edits in the form. (This loses the insertion point, and converts Text to Value.)
If Me.Dirty Then
    Me.Dirty = False
End If

'Unfilter if there is nothing to find. Otherwise, search/filter with wildcards.
If (strText = vbNullString) Or (strField = vbNullString) Then
    Me.FilterOn = False
    Me(strControl).SetFocus 'reselect control
Else
    Me.Filter = strField & " Like ""*" & strText & "*"""
    Me.FilterOn = True
End If

'set the insertion point.
If strText <> vbNullString Then
    Me(strControl) = strText
    Me(strControl).SelStart = lngSelStart
End If
End Sub

Calling the VBA function:

With the above function saved in the current form (press Alt-F11 to open the Visual Basic Editor) you can then call it by attaching a one line VBA routine to the On-Change event of the text control:

Private Sub txtFilterFirst_Change()
FilterFunc "[First Name]", "txtFilterFirst"
End Sub

The function above is a greatly simplified version of Allen Browne’s Find As You Type example, but rather than a drop down list of fields, the code above allows you to just place a separate search box above each column that you want to filter. With a drop down list there will often be fields on the form you don’t want or need to be searchable, so the code has to accomodate an exclusion list.

If you place multiple filters on the same form than you may also want to add some events that clear the other search boxes, so that only the current filter is displayed.

"You may not control all the events that happen to you, but you can decide not to be reduced by them” ~ Maya Angelou

Related

Defaults - How to set form default properties.
Functions - Access functions in SQL or VBA.