Microsoft Access: Applications and Utilities

Provided by Allen Browne, August 2006.  Updated April 2010

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:

• a combo where you choose which field to match,
• a text box where you enter the value to find.

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.

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

Limitations

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

It avoids the control if:

• It is not visible (since the user can't see it.)
• It is not a combo or text box (where a value can be typed.)
• It is not bound to a field (since you cannot filter unbound controls.)
• You told it to exclude this control (in configuration.)

It also avoids controls bound to these data types:

• Yes/No
• OLE Object
• Binary (calculated field of indeterminate type)
• GUID (replication id)
• complex data (attachment or multi-value fields - Access 2007 and later.)

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.

Installation

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:
       =FindAsUTypeLoad([Form])
6. Repeat steps 4 and 5 for any other forms.

Notes:

• Do not rename the controls at step 4. The code looks for those names.
• Step 5 is exactly as shown; do not use your form name in place of "Form".
• If your form has its On Load property set to [Event Procedure], you can add this line to the existing code:
      Call FindAsUTypeLoad(Me)

Configuration

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
to:
    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.

Troubleshooting

If you have difficulties, this might help.

How it works

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