Provided by Allen Browne, July 2008. Last updated: April 2010.
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:
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.
The sample database is concept-only. You will need to develop it further to use it with:
As well as the usual controls bound to fields, the continuous form shown above has:
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:=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|