Provided by Allen Browne, August 2006. Updated April 2010
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.
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:
Notes:
You can use the utility as it is. The options below let you configure how it works.
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")
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
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 = "*"
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 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.) |
The next page explains how it works, including the code.
Home | Index of tips | Top |