Microsoft Access: Applications and Utilities

Provided by Allen Browne, July 2008.  Last updated: April 2010.

Search form, with matching characters highlighted in red

Highlight matches

Can you create a form where search results are highlighted within the text box?

You can in Access 2007 and later, as text boxes can display rich text.

Download the sample database (ACCDB, 41kb zipped.)

To use it:

1. Select a field in the combo.
2. Type the characters to match in the text box.

When you press Enter, the form filters to those records where the characters are found in the field, and displays a text box showing the matching characters in red.

Limitations

The sample database is concept-only. You will need to develop it further to use it with:

• Multi-valued fields, Attachments, or OLE Objects (error);
• Combos where the display value is not the bound column (doesn't match);
• Memo fields where rich text is stored (tags may clash);
• Non-numeric characters (dollar sign, date-separator, etc) in formatted fields (Date, Number, Currency, Yes/No.)

How it works

As well as the usual controls bound to fields, the continuous form shown above has:

• cboField: an unbound yellow combo in the Form Header, where you select a field from a value list. (The code assumes the controls and fields have the same name.)
• txtSearchText: a unbound yellow text box in the Form Header, where you enter some characters to match.
• txtSearchDisplay: a gray text box in the Detail section, to display highlighted matches.

The idea is to include an HTML tag in set the ControlSource of txtSearchDisplay to highlight the search characters. Since its TextFormat property is Rich Text, Access knows what to do with the HTML tag.

For the example above where we searched the CompanyName field for the characters "AR", the ControlSource will become:

In practice, Replace() cannot cope with Nulls, so we must test for Null. And for demonstration purposes, we used a red font tag instead of bold. The expression ends up as:

The AfterUpdate event of cboField repositions txtSearchDisplay over the top of the chosen field (by setting its Top and Left properties.) At design time, we used Bring To Front (on the Arrange tab of the Form Design Tools ribbon) so it sits in front of the other controls.

The AfterUpdate event of txtSearchText applies/removes the form's Filter, and shows/hides txtSearchDisplay with the right ControlSource.

Finally, we set the TabStop property of txtSearchDisplay to No, so it does not receive focus as the user tabs through the form. And just in case the user clicks on it, we use its Enter event to SetFocus to the text box bound to the real field. (Naturally, this suppresses the highlighting, but only on the current record.)