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
Defaults - How to set form default properties.
Functions - Access functions in SQL or VBA.