Agee Software, Inc.

Filter / Find Box for Access Form

Here is how you can add a find box to a continuous form that lets you search multiple text, numeric, date or boolean (Yes/No) fields. The sample form is called frmContact with a recordsource called qryContact. For this example, the SQL for qryContact is:

SELECT * FROM tblContact

tblContact contains fields: ContactID, LastName, FirstName, Street, City, State, Zip, Phone, StartDate, Sel. ContactID is numeric, StartDate is a date field. Sel is a Yes/No field.

  1. Create a textbox named txtFind at the top of frmContact. Set its label to Find.
  2. Modify qryContact to add this Where statement (It should all be one line):

    SELECT * FROM tblContact WHERE '|' & ContactID & '|' & LastName & ', ' & FirstName & '|' & Street & '|'
    & City & ', ' & State & ' ' & Zip & '|' & Phone & '|' & Format(StartDate,'mm/dd/yyyy') & '|'
    & IIF(Sel,'SEL') & '|' LIKE '*' & Forms!frmContact!txtFind & '*'

  3. Add a line of code to the AfterUpdate event of txtFind to requery the form:

    Me.RecordSource = Me.RecordSource

  4. Add the following code to the GotFocus event of txtFind to clear the Find box and show all records.

    On Error GoTo 0
    If Me!txtFind > "" Then
    Me!txtFind = Null
    txtFind_AfterUpdate
    End If
    Me!txtFind.SetFocus
  5. To help the user remember how to use the Find, set the StatusBarText property of txtFind to:

    Find in: |ContactID|LastName, FirstName|Street|City|State|Zip|Phone|StartDate|SEL|

    You can also set the ToolTip property of txtFind and lblFind to this value.
The vertical bar delimiters let you specify the beginning or end of a field.