Highlighting search matches in Access 2007

        4 votes: *****      6,058 views      0 comments
by Allen Browne, 18 May 2009    (Access 2003)

Microsoft Access: Applications and Utilities

Provided by Allen Browne, July 2008


Search form, with matching characters highlighted in red

Search form

Highlight matches

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

You can in Access 2007, 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 2007 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:

=Replace([CompanyName], "ar", "<b>ar</b>"))

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:

=IIf([CompanyName] Is Null, Null, Replace([CompanyName], "ar", "<font color=""red"">ar</font>"))

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.)


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 'Highlighting search matches in Access 2007'?

No comments yet.

Why not be the first to comment on this article?!

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.