Highlight Text
By Richard Rost
3 hours ago
Highlight All Matching Words for a Search in a Text Box
In this lesson, I will show you how to highlight text that matches a search term within a long text field in Microsoft Access. We will use an unbound rich text box and VBA's Replace function to add HTML formatting around each matching occurrence without changing the original table data. We will also discuss how rich text formatting can affect the technique and why this approach does not work directly in continuous forms.
Werner from Cicero, New York (a Gold Member) asks: Is there a way to highlight only the matching search text in a field instead of highlighting the entire field?
Members
In the extended cut, I will show you how to make text highlighting work in continuous forms and how to highlight multiple search terms, such as "Spock" and "Kirk," at the same time.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, highlight matching text, rich text field, VBA Replace function, HTML font tags, search text highlighting, highlight all occurrences, long text field search, continuous forms, multiple search terms, conditional formatting, unbound text box
Subscribe to Highlight Text
Get notifications when this page is updated
More InformationTranscript Although Microsoft Access has conditional formatting that can highlight an entire field when it contains your search text, sometimes that's not good enough. If you've got a big notes field with several paragraphs of text, you still have to hunt around to find exactly where that matching word or phrase is.
Welcome to another TechHelp video brought to you by Access Learning Zone. I'm your instructor, Richard Rost.
In today's video, I'm going to show you how to highlight just the matching search text inside a field. We'll take a regular long text field, display the results in a rich text field, and use a little bit of VBA to highlight every occurrence of whatever you're searching for. We'll do it without changing the original data in your table.
Today's question comes from Werner in Cicero, New York, one of my Gold members.
"Yes, is there a way to highlight only the matching search text in a field instead of highlighting the entire field? For example, if I'm searching a large notes field, I'd like to highlight just the matching portion. This would save a lot of time looking through a large amount of text."
Yes, of course. There's a very simple technique if you know a little bit of VBA. So, if you're new to VBA, go watch this video first. It'll explain everything you need to know to get started in about 20 minutes.
I'd also suggest you go watch this video where I show how to highlight entire fields that match your search using conditional formatting.
I also have this video that shows how to find text in a text box. This is a little different. This doesn't highlight every single instance, but it will select the first instance. Then you can hit Find Next, and it'll move to the next one, and so on. Whereas today, I'm going to show you how to highlight all of them in one shot.
But this one's still pretty useful, especially if you want to do a replace operation. And this is also another good video for searching multiple fields.
I've got tons and tons of search and how-to-find-stuff-in-your-database videos because that's mostly what a database is for. We toss a whole bunch of junk in it, and then the key is figuring out how to find what junk you need later.
So, I've got lots of search videos. These are all free videos, by the way. They're on my YouTube channel. They're on my website. Go watch these if you want to, and then come on back.
Here I am in my TechHelp free template. This is a free database. You can grab a copy off my website if you want to. In here, I've got a customer form, and the customer form's got a big notes field. That's long text.
Now, for the purposes of class, I'm going to just make this guy really big. So, we're going to get rid of a lot of stuff here. I'm going to get rid of this. We're going to get rid of that. We're going to delete you. I'm going to leave one of those buttons so I can steal it later.
Let's put the notes field down here and make it nice and big so we can see the whole thing.
Now, this is just plain text. It's not rich text. If you don't know what rich text is, it has nothing to do with me. Richard, get it? Rich text. Ha, ha, ha.
But if you don't know what rich text is, you're in the wrong class. That's the beginner class down the hall. No, seriously, go watch this if you don't know what rich text is.
Now, this technique that I'm going to show you will work with rich text, with one caveat that I'll explain in a little bit.
But what I'm going to do for the purposes of class is put a little search box over here. So, we're going to just copy, paste. I'm going to slide you over here. Right there.
And the bottom always explodes like that. So, we're going to do that. And I'm going to make this guy white so we know it's a search box. The highlighting shows up better if the background's white.
We're going to hijack this button, and we're going to put this in here for our search. And we need a search field too.
Actually, let's move the button over here. I'm going to relabel you Search, and let's steal this text box. Copy, paste, copy, paste. There we go. I have to do it really fast. Something on my system makes me have to do it really fast.
We can delete the label. We'll just stick this little box right here. Open you up. I'm going to name this guy Search, and we'll get rid of the control source because we don't want it bound to Last Name anymore. It's just an unbound box now. We can put whatever we want in there.
Save it.
Now, I'm going to call this guy Notes2, and I'm also going to make this one unbound so we can put whatever we want in the box. It's not bound to our Notes field. You'll see why in just a second.
And I'm going to switch this guy on the Data tab. We're going to switch this over to Rich Text. So this one can display rich text now. Rich text with all the highlighting and the colors and the fonts and all that good stuff.
Now, just to show you what's going on behind the scenes, we're going to make one more box. Let's make this guy about half size for now, just so you can see what's actually happening in the box.
I'm going to copy this guy. Copy, paste. And let's call this guy Notes2PlainText, and let's switch it back to Plain Text. You'll see why in a minute. This will make sense in just a second.
Save it. Close it. Close it.
Open it.
Now, remember, these aren't bound to anything. So I can just freely type stuff into here. I can freely type stuff into here.
Now, I want this guy to reflect what's in here, but I want to be able to see the actual characters that are in here. So, if I highlight something, for example, I want to see what happens down here when I apply that formatting code.
So, let's come back into here real quick and let's make this guy bound to Notes2. That means whatever I put inside of this box will show up down here.
Save it. Close it. Close it.
Open it.
So, if I type in stuff, it appears on the - oh, wait a minute. Now, I'm seeing these little codes because this is a plain text box that's bound to a rich text box. So all the rich formatting is going to show up down here, and you can see what's going on.
I love Mr. Spock.
Now, if I highlight Spock and move off of it, look what happens. You can see the highlight code in there. Let me zoom in so you can see that better.
See? Font style, background color, blah, blah, blah. There's a color code, and then an end tag font.
Now, here's what we can do. We can apply this ourselves to whatever the search text is and then put that in the rich text box.
So, I'm going to copy this to my clipboard. Give me my clipboard. Where's my clipboard? My clipboard's my handy. Every programmer's friend is Notepad. My little clipboard guy.
Let's do that. Move it off to the side over here.
So now we know what code we have to put in the text to have it work right. So, we've got a search box here.
Now, what I'm going to do is, when I click this button, I'm going to say, "Okay, take this text, find whatever I'm searching for, and replace it with whatever I'm searching for with that extra HTML tag around it."
And if you don't know the Replace function, you can see this video is about five or six years old because my slides were a lot less quality back then.
So, let's go into our code. Let's go into this Search button, which I hijacked from another button. Yeah, we don't need that button.
Now, let me resize this real quick. We can turn that off. We deleted this button, and now we stole this button. That's okay. This is now our Search button.
I'm not going to rename the button. You know how to do all that stuff.
My main Notes data is in the Notes field, so I want to put that in Notes2. So, we're going to say Notes2, which is my rich text box, is going to be equal to Replace whatever's in Notes with my search text.
Find the search text and replace it with - let's bring back our Notepad. Where's Mr. Notepad? Come here.
I want this guy, and then the search text, and then font at the end of it. So, copy that, and we're going to stick that right here.
Now, this has to be inside of quotes. So, we have to go quote here, quote there. And let's make the line continuation character there. And then it's not going to be Spock. It's going to be my search text again. It has to go inside of there. And quote, quote, like that.
So, put this in front of it, then the search text again, and then put that at the end of it.
Now, here's another kicker. We've got double quotes inside of there, and no, you can't replace those with single quotes. Access HTML is not as forgiving as actual real web HTML. So, these have to be double-double quotes. Double-double quotes inside of a string says replace that with an actual double quote.
And yes, I've got several whole videos on my website about using double-double quotes. It's a pain. If you don't know what I'm talking about, go watch this video. We talk about double-double quotes.
So, we're going to take Notes2, which is what's going to go in there, take the big Notes field, find Search, whatever the search text is, and replace it with this guy. Find the search text and that guy.
I'm getting old, but I've got something wrong in here. Hold on.
Oh yeah. Anybody see it? Take a second. If you can't figure it out, I do this one all the time, especially when I do a line continuation.
See it? Pause the video if you need to figure it out.
That's right there. I forgot to close my parentheses.
And see, everybody capitalizes. Notice that everybody capitalizes. I always type in lowercase. I can watch the variables pop up.
And let's throw a beep on there so we know something happened.
Save it. Debug Compile once in a while. Close it. Close it. Open it.
I'm going to come over here. I'm going to type in Amicron and then hit Search.
Oh, look at that. See? It found the Amicron. And you can see it highlighted it here because we put the tags right. And you can see the actual tag down here.
If you want to search for sign, notice this one's lowercase, whereas this one's capitalized. It's going to replace it with whatever's in your search box. So, if you type it in lowercase, it's going to put it in lowercase. But that's just using this to search anyway. That's not a big deal.
And we're not modifying this text. We're just displaying it over here.
And if you've got something that's more - let's just say you put the word he in there - it'll find all of them. See? It finds every instance of he and replaces it for you. Pretty nifty, huh?
Now, I mentioned earlier there's a caveat when it comes to rich text. If this is rich text, let me demonstrate for you.
We have to go back to the table real quick. Well, you don't have to, but normally you have to go to the table, Design View. We're going to switch Notes to Rich Text in here.
Say yes. And then we've got to change the form itself so that this is Rich Text.
So now our Notes field can handle rich text.
And by and large, it's going to work just fine. If I put Florida in here and hit Search, we're still good.
But here's the problem. If you've already got highlighting in here, let's say you've got highlighting that does this already. You've got a different section highlighted, and now you do a search. It's not going to work because our search code is now spanning another search code or an HTML code.
So that's the only downside. It won't work if you're already dealing with rich text and there's already highlighting in it that spans across what you're searching for.
Could we fix that? Yeah, probably, if you want to spend the next hour coding it. I don't.
But this is good enough for doing search. It works really well with plain text, which I think is probably 90% of what most of us are using.
And I guess if you really want to get high speed, you could make another text box that converts this to plain text first and then searches in the plain text. You can strip all the HTML codes out with simple VBA. There's a lot you could do with it, but I'm not doing all that today.
Now, one downside to this technique is it doesn't work with continuous forms.
So, let's say you turn this into a continuous form. So, I think you get rid of this guy. We don't need him anymore.
And let's turn on the form header/footer. I'm going to move the search box up here.
And we're going to do a little gymnastics. We're going to move this around just a hair. We're going to slide you over here. We don't need a lot of this stuff on to make this just a little more compact.
So, let's get rid of that. Get rid of that. Get rid of this.
We're going to put First Name and the Last Name over here just so we can see who it is. Not that we really need it.
We're going to put the Notes field here. We're going to put this guy there. Not there. There. No, not there.
We're going to shrink this up a little bit, and we're going to make this a continuous form so we can see multiple records.
So, we're going to go to the properties of the form, and we're going to change Format, Single Form to Continuous Forms.
Close it. Close it. Open it.
And now we've got continuous forms. You can see multiple people there.
Now, here's the problem. If I come over here and put in Spock and hit Search, I get all the same record. Why is that?
Well, because what we're doing is we're saying, "Okay, make Notes2 whatever is in Notes1," but whatever is in Notes1 gets copied to all of these because this guy isn't bound to anywhere.
And the way a continuous form works is the value of Notes is equal to the value of Notes on whatever record you're on. So, they all get set to that.
So, how do you do it with continuous forms? Well, that's what we're going to cover in today's extended cut for the members.
Silver members and up get access to all of my extended cut videos, not just this one, all of them, and there are hundreds of them. So, hit that blue Join button right now to find out more information on that.
But in the extended cut, I'm going to show you not only how to get it to work with continuous forms, but also how to add multiple search terms.
So, you want to search for Spock and Kirk? Boom. There you go.
You want to look for, I don't know, France? Boom. There's France.
Let's see. It works for continuous forms too. Isn't that cool?
But that's how you highlight matching words in a text box using some cool HTML and rich text boxes.
And that, my friends, is going to be your TechHelp video for today. I hope you learned something.
Live long and prosper, my friends. I'll see you next time, and members, I'll see you in the extended cut. Intro In this lesson, I will show you how to highlight text that matches a search term within a long text field in Microsoft Access. We will use an unbound rich text box and VBA's Replace function to add HTML formatting around each matching occurrence without changing the original table data. We will also discuss how rich text formatting can affect the technique and why this approach does not work directly in continuous forms. Quiz Q1. What is the main purpose of the technique demonstrated in the video?
A. Highlight only the matching search text within a long text field
B. Change the font color of every field on a form
C. Replace matching text permanently in the table
D. Sort records alphabetically by the Notes field
Q2. Why is a separate unbound text box used to display the highlighted search results?
A. To avoid modifying the original Notes data
B. To make the Notes field required
C. To convert the Notes field into a number
D. To automatically save the search term in the table
Q3. What setting must the display text box use in order to show highlighted text?
A. Plain Text
B. Rich Text
C. Hyperlink
D. Password
Q4. What VBA function is used to find the search text and insert the rich text formatting tags?
A. Replace
B. InStr
C. DLookup
D. Format
Q5. In the demonstrated technique, what does the Replace function do?
A. Finds the search text and replaces it with the same text surrounded by HTML formatting tags
B. Deletes every occurrence of the search text
C. Saves the search text to the Notes field
D. Filters the form to only matching records
Q6. Why are double-double quotes used in the VBA string containing HTML attributes?
A. They represent an actual double quote character inside a VBA string
B. They indicate a comment in VBA
C. They convert the string to plain text
D. They force Access to save the record
Q7. What happens if the user searches for a word that appears multiple times in the Notes field?
A. Every occurrence is highlighted
B. Only the first occurrence is highlighted
C. Only the last occurrence is highlighted
D. The Notes field is permanently changed
Q8. Why can this technique have problems when the original Notes field already contains rich text formatting?
A. A search term may span existing HTML tags or formatting codes
B. Rich text fields cannot contain searchable text
C. Access cannot display more than one highlight color
D. The Replace function only works with numbers
Q9. What is the main reason the basic technique does not work properly in a continuous form?
A. An unbound control displays the same value for every visible record
B. Continuous forms cannot contain rich text controls
C. Continuous forms cannot use buttons
D. The Notes field becomes read-only in continuous view
Q10. In a continuous form, what incorrect result occurs when the unbound highlighted-results control is filled for one record?
A. All visible records display the same highlighted text
B. All Notes values are deleted
C. The search box becomes disabled
D. The form automatically switches to single form view
Q11. Which control should remain bound to the actual Notes field if you want to preserve the original data?
A. The original Notes control
B. The unbound Rich Text results control
C. The Search text box
D. The Search button
Q12. What is one advantage of using this highlighting method instead of normal conditional formatting?
A. It can highlight the matching word or phrase instead of the entire field
B. It automatically corrects spelling mistakes
C. It searches every table in the database automatically
D. It permanently adds colors to the table data
Answers: 1-A; 2-A; 3-B; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. Summary Today's video from Access Learning Zone shows how to highlight only the matching search text inside a long text field in Microsoft Access.
Access conditional formatting can highlight an entire control when it contains a search term, but that is not always helpful when the field contains several paragraphs of notes. If a customer notes field contains a large amount of text, highlighting the entire field still leaves you searching through the text to locate the specific word or phrase that matched.
In this lesson, I show you how to display a long text field in a Rich Text control and use VBA to highlight every occurrence of the search text. The original data stored in the table remains unchanged. The highlighting is applied only to an unbound display control on the form.
Before working with this technique, you should have a basic understanding of VBA. You should also understand the difference between Plain Text and Rich Text fields in Access. This method uses Rich Text formatting tags, so the control that displays the highlighted result must be configured to support Rich Text.
I begin with a form that contains a large Notes field. For demonstration purposes, I simplify the form and enlarge the Notes area so there is plenty of room to work with long paragraphs of text.
Next, I add an unbound text box to the form for the user to enter a search term. I also add a Search button that will run the VBA procedure. The search text box is unbound because it is only used temporarily to hold the word or phrase the user wants to find.
I then create a second text box, which I call Notes2. This control is also unbound. It will display the Notes field after the search matches have been marked with Rich Text formatting. Since it is unbound, nothing placed in Notes2 is saved back to the table.
The Notes2 control must have its Text Format property set to Rich Text. This allows Access to interpret HTML-style formatting tags and display highlighted text.
To better understand what Access stores behind the scenes, I also create another text box configured as Plain Text. I temporarily bind that plain text box to the Rich Text control. When I manually highlight a word in the Rich Text control, the Plain Text control reveals the formatting tags Access uses to create the highlight effect.
Those tags include a font tag with a background color and a closing font tag. Once I know the formatting Access expects, I can use VBA to insert those tags around every matching search term.
The Search button's Click event uses the VBA Replace function. The procedure takes the original Notes field, looks for the text entered in the Search box, and replaces each occurrence with the same search text surrounded by the Rich Text highlighting tags.
The result is assigned to Notes2, not to the original Notes field. This is important because the Notes field remains untouched. The form simply displays a highlighted version of the text for searching purposes.
When building the VBA expression, remember that the HTML formatting requires quotation marks. Because the formatting tags are placed inside a VBA string, the quotation marks inside those tags must be represented with doubled quotation marks. This is a common source of errors when working with strings that contain HTML or other quoted text.
It is also important to make sure the Replace function has its closing parenthesis. When you build longer expressions, especially expressions split across multiple lines using a line continuation character, it is easy to overlook a parenthesis. I recommend compiling your VBA project periodically to catch syntax errors before testing the form.
After entering a search term and pressing Search, every occurrence of that term in the Notes field is shown with a colored background in the Rich Text display box. The technique can find multiple occurrences throughout the text, including repeated words within the same paragraph.
The search is not case sensitive in the normal way most users expect. However, because the Replace function substitutes the text from the search box, the displayed replacement text will use the capitalization entered in the search box. For example, if the original note contains "Spock" but the user searches for "spock," the displayed highlighted version may show the replacement in lowercase. Since this is only a temporary search display and does not alter the underlying data, this is usually not a major concern.
This method works especially well when the original Notes field is Plain Text. It can also work with a Rich Text Notes field, but there is an important limitation.
If the original text already contains Rich Text formatting, such as existing highlighting or other tags, a search phrase may span across those formatting tags. In that situation, the simple Replace approach may fail because the matching text is no longer stored as one uninterrupted sequence of characters. Solving that problem is possible, but it requires more advanced code to account for and preserve the existing HTML formatting.
For most databases, notes are stored as Plain Text, and this technique works very well. If necessary, you could also create a process that strips Rich Text tags, converts the content to plain text for searching, and then displays a highlighted search result. That is beyond the scope of this basic technique, but it is an option for more advanced applications.
There is another limitation to be aware of. This simple method does not work properly on a Continuous Form.
In a Single Form, each unbound display control shows the highlighted version of the Notes field for the current record. In a Continuous Form, Access reuses the same unbound control across all visible rows. If you place the highlighted Notes2 control in a Continuous Form and run the search, every row can end up showing the same text from the current record.
That happens because an unbound control on a Continuous Form does not maintain a separate value for each displayed record. Access treats it as one control repeated visually for every row.
Also, in today's Extended Cut, we will make this technique work properly with Continuous Forms. I will also show you how to search for multiple terms at the same time, allowing you to highlight several different words or phrases in the notes field.
This is a useful technique whenever you need to search large notes fields, comments, descriptions, or other long text values in Microsoft Access. By using an unbound Rich Text control and VBA's Replace function, you can give users a much clearer visual indication of exactly where the matching text appears without changing the data in the table.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends. Topic List Creating an unbound search text box
Displaying search results in an unbound rich text box
Viewing rich text HTML formatting tags
Highlighting search text with the Replace function
Using HTML font background color tags in VBA
Escaping double quotes in VBA HTML strings
Highlighting every occurrence of a search term
Preserving original notes data during highlighting
Rich text limitations with existing formatting
Why this technique does not work in continuous forms Article Microsoft Access can highlight an entire control with conditional formatting, but that is not always helpful when you are searching through a long Notes field containing several paragraphs. A better approach is to display the note in a Rich Text text box and insert formatting tags around each matching word or phrase. This lets Access highlight only the matching text while leaving the original stored note unchanged.
The basic idea is to keep your original Notes field as the source data and display a separate, unbound Rich Text text box for the search result. You also need an unbound search box where the user can type the word or phrase they want to find, along with a Search button.
The result text box must have its Text Format property set to Rich Text. This is important because a plain text text box will display HTML-style formatting tags as literal characters instead of applying the formatting. The original Notes field can remain plain text, which is usually the simplest and most reliable setup.
When the user clicks Search, VBA should take the value from the original Notes field and look for every occurrence of the text entered in the search box. It should replace each matching occurrence with the same text wrapped in Rich Text formatting tags that apply a background color, such as yellow. The modified text is then assigned to the unbound Rich Text result box.
The original Notes field is never changed. The formatting exists only in the displayed result text box, making this safe for searching and reviewing notes without modifying the actual table data.
For example, suppose a note contains the sentence "Customer spoke with Mr. Spock regarding the shipment." If the user searches for "Spock," the code should locate that word and surround it with the Rich Text tags needed to apply a highlight color. Access then displays the word Spock with the chosen background color while the rest of the note remains unchanged.
The VBA logic is fairly simple at a high level. It should first confirm that the search box contains text. It should then read the current record's Notes value, perform a text replacement for every occurrence of the search phrase, and place the resulting Rich Text content into the separate result control. If no search text is entered, you may choose to display the original note without highlighting or clear the result box.
Be aware that replacing text may also affect capitalization. If the replacement logic uses the search term exactly as typed in the search box, a user searching for "spock" may cause an originally capitalized occurrence such as "Spock" to be displayed as lowercase in the result. This does not alter the source data, but it can affect the temporary displayed result. If preserving the original capitalization is important, the code needs a more advanced matching method.
This technique works best when the original Notes field contains plain text. It can also work with Rich Text source data, but there is an important limitation. If the note already contains HTML or Rich Text formatting and the search phrase crosses existing formatting tags, a simple text replacement may fail or produce unexpected results. For example, if part of a word or phrase is already bold, colored, or highlighted, the stored Rich Text markup may interrupt the phrase and prevent a straightforward search from finding it.
For most databases that store ordinary plain text notes, this is not a problem. Plain text notes are ideal for this approach because the search process can work directly with the visible text and then add only the highlight formatting needed for display.
This method also has a limitation in continuous forms. An unbound text box in a continuous form does not maintain a separate independent value for every displayed record. If you assign a result value to that unbound control, Access may display the same result in every visible row. To support highlighting in a continuous form, the highlighted text usually needs to come from the form's record source, such as a calculated query field, a temporary table field, or another record-specific method that gives each row its own result value.
For a single-record form, however, an unbound Rich Text result box is a practical solution. It gives users a clear visual indication of every matching occurrence in a long note, avoids altering the original data, and makes searching large blocks of text much easier. Primary Topics Access rich text controls, VBA Replace function, highlighting all search matches, unbound display textbox, long text/notes fields, HTML formatting tags, search textbox and button Secondary Topics conditional formatting comparison, finding versus highlighting text, double-double quotes in VBA strings, plain text versus rich text source data, continuous forms limitation |
