Microsoft Access: Applications and Utilities

Provided by Allen Browne, August 2006.  Updated April 2010







How it works

Find as you type

This utility finds matching records with each keystroke you type. Access 2007 and 2010 provide this functionality in its datasheets, as other software and browsers do.

It consists of two controls:

You can use these controls on any form by adding one line to the form's Load event procedure.

The screenshot below shows how it matches multiple results in a Continuous Form. It also works in Form view.

Find as you type screenshot

Download the sample database for Access 2000 and later or Access 97 (50KB, zipped.)


The code examines your form, and identifies which controls to list in the Filter the field combo.

It avoids the control if:

It also avoids controls bound to these data types:

The filtering of combo boxes depends on your version. All versions include the combo if its BoundColumn is the display column. Otherwise the combo can be filtered only in Access 2002, and only if the RowSourceType is "Table/Query."

Memo fields in Access 97 and Rich Text fields (in Access 2007 and later) may not filter correctly.


To add this functionality to your database:

  1. Grab the version for Access 2000 and later or Access 97.
  2. Copy the module ajbFindAsUType into your database.
  3. To verify Access understands it, open the code window, and choose Compile on the Debug menu. (In Access 2000 or 2002, you may need to add the DAO reference.)
  4. Copy the combo and text box from the form in the sample database, and paste them into the Form Footer section on your form.
  5. Set the On Load property of your form (Event tab of the Properties box) to:
  6. Repeat steps 4 and 5 for any other forms.



You can use the utility as it is. The options below let you configure how it works.

Suppressing fields

If there are controls you do not want to offer for filtering in your form, list them in the form's On Load property. Use quotes around each control name, and commas to separate them.

For example, to suppress filtering for Combo1 and Text99, set the form's On Load property to:
    =FindAsUTypeLoad([Form], "Combo1", "Text99")

Start-of-field matching

The utility finds the text anywhere in the field. To match the beginning of the field only, open the module in design view and change the line:
    Private Const mbcStartOfField = False
    Private Const mbcStartOfField = True

Wildcard character

Access uses the asterisk as the wildcard character. Tables attached from a different back end may need a different character, such as the percent character. Specify the character you need inside the quotes on this line:
    Private Const mstrcWildcardChar = "*"

Error logging

For simplicity of installation, the code uses a generic error handler that does not log the errors. If you would prefer true error logging, replace this function with the one in this article: Error Handling in VBA.

Access 97 compatibility

Access 97 did not have the Split() and Replace() functions, so the Access 97 version of the utility has custom functions with these names.

If you update an Access 97 database to 2000, you can comment out these two functions. The later version includes these two functions already commented out, in case you need to copy the code back to an old Access 97 database.


If you have difficulties, this might help.

Problem Resolution
I put the controls on my form, but they do not show up when the form is opened. 1. Your form must be bound to a table or query.
2. Both controls (cboFindAsUTypeField and txtFindAsUTypeValue) must be unbound.
The form does not filter when I type a value. Check the form's On Load property is set correctly.
My fields are not available in the combo. See limitations above, to see if the controls should be offered.
I get an error when the form loads. See step 3 under installation above. You may need to add the DAO reference.
The combo shows the wrong name for a field. Attach a label to the problem control, and set the Caption of this label to what you want. (Set the label's Visible control to No if you wish.)

How it works

The next page explains how it works, including the code.

Home Index of tips Top