Find as you type

        14 votes: ****      16,632 views      2 comments
by Allen Browne, 06 September 2006    (Access 97+)

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.

Find as you type screenshot

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.

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

Rate this article:   Your rating: Poor Your rating: Not so good Your rating: Average Your rating: Good Your rating: Excellent


This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Find as you type'?


1.

Kelly Wornell says...

09 April 2008

 
The line "Private Const mbcStartOfField = True" can also be set to False, and that is a great tool.

Is there a way to control this with a check box on a form? i.e. a user sometimes want to search for a value anywhere and then next search wants to only use that value at begining.

2.

Dave Tunstell says...

11 April 2008

 
This would be possible with a small tweak to the code. First, change the private constant definition into a public variable:

Public mbcStartOfField As Boolean

Then for your check box AfterUpdate event, try:

Private Sub MyCheckBoxName_AfterUpdate()
mbcStartOfField = MyCheckBoxName.Value
Call FindAsUTypeChange(Me)
End Sub

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments
Verify Code
Verification Code


It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.  

All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.