Search Matches
By Richard Rost
4 hours ago
Highlight Which Fields Match Your Search In this lesson, we will walk through creating a multi-field customer search form in Microsoft Access that identifies which fields matched the user's search term. You will learn how to build a query that searches first name, last name, and notes fields, use conditional formatting with the InStr function to highlight matching text fields, and use VBA to load search results only after a search is entered. Russell from Omaha, Nebraska (a Silver Member) asks: I have a customer search that looks through several fields, and it does return the right records. But when someone searches for a word like "Smith," they can't tell whether it matched the customer's name, company, address, or notes without scanning the whole row. Can I make the reason each record matched more obvious? MembersIn the extended cut, we will learn how to search across related parent and child tables, including customer records and contact history. I will show you how to add a second search box to further filter related records, use more VBA and SQL directly in forms, and keep the parent-child relationship organized in the search results. 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!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, multi-field search conditional formatting, highlight matching fields, search result highlighting, InStr function, wildcard search query, dynamic record source, search form VBA, SQL WHERE 1=0, multi-field search box, conditional formatting expression
Subscribe to Search Matches
Get notifications when this page is updated
More InformationTranscript Ever search for something in your database, get the right records back, but then have to stare at all the results wondering, okay, but where did it find that?
When you're searching through several different fields, that can turn into a game of Where's Waldo.
Welcome to another TechHelp video brought to you by Access Learning Zone. I'm your instructor, Richard Rost.
Today, we're going to make a multi-field search a lot easier for your users to understand. Instead of just returning records that match a search term, we'll make it clear which fields in the record actually contain that match.
So whether the text appears in a name, company, address, or more than one place, your users can easily see the reason that that record showed up without having to hunt all over the form.
Today's question comes from Russell in Omaha, Nebraska, one of my Silver members.
Russell says, "I have a customer search that looks through several fields, and it does return the right records, but when someone searches for a word like Smith, they can't tell whether it matched the customer's name, company, address, or notes without scanning the whole row. How can I make the reason each record matched more obvious?"
Well, Russell, that's not too hard to do. Yes, your final search should not only show all the records, but it should show exactly where that data showed up.
So let's see why this matters and how we can do something to fix it.
So here's the situation that comes up all the time. You build a nice search form with one little search box, and I've done several different videos on how to make a multi-field search form. I'll give you some links in just a minute in case you don't know how to do this.
You got one little search box up top, you type in whatever you're looking for, and it'll search across multiple fields. Access brings back the correct records. So great, everybody's happy.
Well, not necessarily.
Suppose you're searching customers and the user types in Smith. Now that word could be in the customer's first name. I know guys with first names that include Smith. Last name, company name, address, could be in the notes field, and your notes field might be long. It might just be showing a tiny piece of it on this form, so it could be buried a paragraph deep in the notes, but yet it still looks like it's showing up for that customer record.
So now not only do you have to scan across the entire row looking for the word, but it could be buried in the notes box somewhere.
So what we're going to do today is we're going to make it so the user can type in the search, see the records that match that term, whether it appears in any of those fields. But then, instead of having to inspect every field manually, the matching fields will stand out visually with a little bit of conditional formatting.
So there's no reason to make the user guess. It'll just all be nice and apparent for you.
All right, so before we get started with the walkthrough, if you've never done any VBA programming before, go watch this video. It's about 20 minutes long. It'll teach you everything you need to know to get started. We are going to use a little bit of code today, not a ton.
This is one of my videos where I teach you how to search multiple fields with a single search box. Go watch this. This is a good one.
If you don't know how to use conditional formatting, go watch this guy. And most importantly, go watch the Expression Is video because we're going to use an expression in conditional formatting, which is a little more complicated.
You should know what a record source is. You should know how to do a wildcard search with the Like keyword. You should know about And, Or, and Not, and my famous "And across, Or down" phrase. And searching for criteria in a query. And across, Or down. Very important.
We're going to use the InStr function to locate a string within another string. We're going to use a little bit of SQL, so you should know some SQL.
And I think that's about it. If there's more, I'll throw up the slides as we get there. But these are all free videos. These are on my YouTube channel. They're on my website. Go watch any of these that you're not sure of, and then come on back and see me.
All right, here I am in my TechHelp free template. This is a free database. You can grab a copy off my website if you want.
And in here, we got customers, and customers got first name, last name, address, notes, all the fields that you might want to search in.
Now, I've already got this customer list form. I'm going to keep this the way it is, but let's borrow this and turn it into a search form.
So I'm going to take my customer list form. We're going to copy and paste and call this the customer search form instead.
Customer Search Form. Let's design view.
And right out of the box, let's just really quickly change the color so we don't confuse it with the other guy. So let's just change the format here. We'll make this guy green instead.
I like to make all of my forms have a unique, if not similar, then close to the same color, but enough that you can tell what different field you're working with here.
So we got first name, last name, and I'm going to get rid of state and customer since. We don't need these, and let's get rid of this here too. Let's change this guy over to notes because notes is the good one for searching.
That's going to be notes. Don't forget to change the control source and the name.
And I think this is enough to do what we want to do, just these three. You can make as many fields on here as you want. These are enough to demonstrate what we need to demonstrate.
Okay, notes. Save it. Close it, close it, open it back up again, take a peek.
All right, looks good.
And one thing that I've learned over doing this and building databases for 30 years is that you can never have too many different ways to search. I've taught a bunch of different videos on search methods, and I never get all the search stuff, because that's the whole point of a database. You want to put a bunch of crap in it, and you want to be able to easily find that crap when you need it.
I've got a task database that I use, and I'm like, "When was I? I know I said something about Quick Queries yesterday. Okay, what was it?" So then I just go search Quick Queries, and I've got 5,000 records. Okay, now I got it limited to yesterday's.
So that's the whole point of a database. So searching will never get old. I love doing search videos because there's always a new twist or a new technique or something cool I can show you.
All right, so let's put a search box on top of this guy. So up here in the header, let's make some room, and I'm going to move these labels down.
Grab one of these fields, doesn't matter which one, copy, paste it, put it up here on top. This will be our search box.
So open this guy up. Now get rid of the control source because it's not bound to anything. It's an unbound box, and let's change its name to just Search.
And if you want to put a label next to it, that's fine too. Here, let's grab a label. We'll stick a little label up next to it.
So let's do a search. If you want to attach it to the text box, we can do that too. You just cut it out and then paste it, and it sticks to it. But my copy and paste is not working right, so I'm going to undo that.
Something with the screen recording software that I'm using. If I go copy, let's see, cut, paste. If I do it really fast, sometimes it works. But I'm not going to hold up the class for that anyways.
So there's our search box. That guy's named Search.
Let's move these guys back up just a smidge.
Now I'm going to make a query so that if this search text appears in either first name, last name, or notes, then the record shows up here.
So for that to work, we need to make a query. Save changes.
Let's go and create a query, Query Design. I'm going to bring in the customer table. If you got multiple tables involved here, you can do that as well. In fact, I'm going to show a technique for a parent-child relationship in the extended cut for the members. But for this one, we're just going to do a single table.
Customer ID, first name, last name, and notes. Bring over the fields that are in your form. Don't bring over too many fields. You don't need all these things. Don't bring down the star if you're only looking for three fields.
Especially if you're dealing with a network or using SQL Server over the internet or an S stuff, the more fields you add down here that you don't need, the slower this query will get. We're going to talk about a technique for that in just a second too.
Save this as my CustomerSearchQ. That's my search query.
Now down here for the criteria, we want this to be whatever's in the wrong one, search for it, SearchF, whatever's in this box. But I don't want equal. I want a wildcard search.
So back over here. So in this criteria box, let me zoom in for you. Shift+F2. Whoop, my Shift+F2 got really big. Let me resize this. There we go.
This should be Like, and then it's going to be star and Forms!CustomerSearchF!Search and a star. So I don't want exactly what's in that box. I want whatever's in that box surrounded by asterisks.
So if it's Smith, I want everything that includes Smith.
Hit OK.
Now here's the important part. If you do it across like this, that means this has to be true for each one of these fields in an And condition. So it's got to be first name and last name and notes. I don't want that. I want an Or condition. So that's what the down is for.
So we're going to move these down. Cut, paste. Cut, paste.
So now it says first name has to match our condition, or last name can match our condition, or notes can match our condition.
Save it.
Now if I run it now, where's the query? Search query right there. Okay, good. I get all the records.
If I put something in here like, let's just put in here, Rost. Click off of it for a second, and then run it. Now I just see Rost. So now it's working.
Now, the problem a lot of people run into is that when they do this step, they'll put in something in here like RICH, and then they'll run this when they're testing it, but I'm still getting Rost. Why is that?
Well, because this value hasn't saved yet. You're still in that field, so you have to leave it.
Which isn't a problem because in a minute, we're going to tie this update to an After Update event. And by that point, you've left the field. But while you're testing it, everyone's like, "It's not working while I'm testing it." Well, that's because you're still in the field.
Now see, I typed in rich, and I don't see it there. I don't see it there. I don't see it here. Probably in this notes field somewhere. Let's see, Shift+F2. It's probably rich in here somewhere. Do you see it? Yep, there it is. So it's working.
But that highlights the need for why we're doing what we're doing right now.
So let's set up the conditional formatting first. Actually, let's tie this form to that query first.
Oops, someone's beaming in.
Let's tie this form to the query. So we're going to change the record source up here from CustomerT to the CustomerSearchQ.
Close it. Close it.
And I don't have a button for it yet. You know what we're going to do? We're going to make a button real quick.
Let me move these guys down out of the way. And I'll move you down. And we'll just copy, paste. See, my copy and paste works if I do it fast.
Let's sit down. We'll make this one the customer search.
Click Build Event. And yes, my VBA editor is in dark mode right now.
Customer search.
DoCmd.OpenForm "CustomerSearchF"
Save it. Close it. Close it. Open it.
And yes, for those of you who are new, I do have a button here on my Quick Launch toolbar that just runs a macro that opens up my main menu. That's down here. There's a separate video for that on my website.
But now I got a button that opens this guy up.
Now if I come in here and type in rich, if I come down here now and hit F5, it'll requery. I'm going to show you how to do that with some code in just a minute. But for testing purposes, that's how we can test that this thing is working.
Come up here, go to Rost, click down here, hit F5. Beautiful. So it's working.
Now let's tie in the conditional formatting. So we can see which field triggered that.
So Design View.
Now we'll do one field at a time. We're going to copy and paste the equation, but that's going to be unique for each one.
So open up first name. You don't need this. Go to first name. Go to Format. Conditional Formatting. New Rule.
Now it's not Value Is because there's none of these that really match that. Excel actually has some more in-depth options in here, like contains and stuff like that. But this one just has between, but not equal to, equal to, greater than, all.
So we need to make an expression. An expression allows us to put more stuff in here.
Now, what I want to say here is if my search term appears in this particular field, then I want to apply my conditional formatting. And to do that, we'll use the InStr function.
This is going to be InStr.
Now what am I looking inside of? Well, I'm looking inside of whatever this field is. So this is first name. Now make sure you put it inside square brackets: [FirstName].
I know I always tell you guys you don't need square brackets if you don't have spaces in your names. But this is one of those rare instances where you still need the square brackets.
Comma. What am I looking for in first name? Well, I'm looking for whatever's in the search box, just like that. And again, square brackets.
And if that comes back with anything but zero, if it's higher than zero, that means it found that text inside FirstName.
So take this, stick it in here, and then let's give it a format, like yellow.
Hit OK. Hit Apply.
And if you forget the square brackets, you'll notice that these change to have quotes around them. So be careful about that. I talk about that in the conditional formatting video.
Hit OK.
Let's just test this one for now. Save it. Close it. Open it. Type in a Richard or Rich. Let's do Rich. Oh, look at that. Right off the bat. Boom. See? There we go.
Now we haven't done notes yet, so let's do the other ones.
Back into here. Open up this guy. Format. Conditional Formatting. New Rule. Expression Is. Paste it. Change this to last name. Give it a format. Color. Hit OK. Apply.
And then do notes. Same thing.
Yeah, you could do the format to all of them and then just come in here and change just the field name. That's another way to do it too. That might have been easier, but I did it the hard way.
Notes.
All right. Save it. Close it. Open it. Put in here, Rich, and then we'll refresh. F5.
And look at that. Now it's showing up. Now you can see why Rich triggered this particular search.
So now we're ready to put some VBA in this thing to make this whole thing work a little better for the user.
So let's right-click Design View.
Now the first thing that I like to do, especially for a search form, is I don't want the search form initially loading any records at all. And this is especially handy if you're working over a network. You don't want it loading up a whole bunch of records if you don't need them yet.
So what I'm going to do is I'm going to set the initial record source equal to an impossible result, an impossible query. So I'm going to say, "Select Star from CustomerSearchQ where 1 equals 0."
That Where condition is never true, so you'll get no records. And that's perfect for a search form. You want it to start blank.
Save it. Close it. Close it. Open it.
Empty records. You see? Nothing in there. Beautiful.
Now the user could type in a search result, and then in the After Update for this guy, this box right here, Event, After Update, this is where we can change the record source of the form to the actual query that will provide the results.
Me.RecordSource, not Me.Recalc, Me.RecordSource equals "CustomerSearchQ", just like that.
And changing the record source is enough to requery the form. You don't need a separate Me.Requery here.
Debug, Compile once in a while. Close that. Close it. Close it. Oh, save changes. Yes. Close it. Close it. Save it. Yep.
Okay, here we go.
Now I can come in here and type in rich and just hit Tab or Enter. Boop. There you go. There's your results. Isn't that handy?
Want to change it? Come over here and put in a John like that. There's a couple of Jean-Luc Picards. Or a John. That's what this is. John in here, that means, let's see. Open it up. See right there under Jean-Luc Picard. Perfect.
So we know our search results are working.
Want to put a little box here? I like a little box here to clear the results. That's something that's nice to put, a little box here.
Let me grab this. Copy, paste. I'm going to put a little X in here for the caption. Resize it. Stick it up here. That just tells the user you can clear those results by just clicking on that little button.
I click Build Event.
Now, yeah, Alex taught me a while back to always name my buttons. And I do most of the time. If I'm going to do anything with this button, I'll give it a good name. But if I'm going to change its caption, like someone else is going to change its caption or its size or its color, then I'll give it a good name.
But until then, ClearSearchFilterButton is good enough for me. I can easily see it. I know what that is. Just scan it through the code.
And here, all we're going to do is we're going to set our form back to the impossible search query, which is what we put in here. Data, this guy. Just copy that.
And we're just going to say Me.RecordSource equals that impossible query again.
Do you need the semicolon here? No, it's not going to hurt anything if you put it there. Get in the habit of leaving it there if you want to. If you're going to be using SQL Server, you can use it there for multiple commands in the same query.
All right, ready? Close it. Close it. And open it.
We're going to put rich in here again. And now we'll just clear the results. Oh, look at that. Isn't that special?
Now let's take it further in the extended cut for the members.
We're going to take this form to the next level. We're going to add related records. What if you want to search in the user's customer information or their contact data? You want to search in all their notes, all the stuff that you've talked about over the past year. Then tell your contact history. Maybe you want to search their order history.
Oops, someone's beaming in.
Right now, we're just searching in a single table, the customer table. But in a real database, you've usually got information spread across multiple related tables.
So we'll set this up so our search can find matches both in the parent customer record and in its related child records. And we're even going to add a second search box too, down here. I haven't done it yet. This is just a mockup.
But we'll take another search box and put the search box down here. So once you get the overall search, let's say you're just searching for Richard. Richard's going to come up in all these records too. So now we can whittle that down further. Say, okay, for Richard, I want to find every contact record that mentions pizza, let's say. And then you can whittle that down even more.
And by the time we're finished, we're going to have a much more powerful search interface that can cross related tables and keep the parent-child relationship all clean.
We'll do a little bit more VBA in this one, put some SQL directly in the forms, and it's going to be good fun. It's going to be a party. You're invited. You just got to click that blue Join button.
All my Silver members and up get access to all of my extended cut videos. Not just this one, all of them. There's thousands of them. There's lots of them. I don't know if it's thousands of extended cuts yet. It's definitely into the hundreds now. I know we're over a thousand regular TechHelp videos. I got to add that up one of these days.
So click that blue Join button. If you want to learn more, you can either join here on YouTube or on my website or wherever you want. That's lots of options.
And if you want to learn more about searching and sorting in my Access Developer 26 video, we make a really cool search box where you can have the user put in whatever field they want to search, the conditions or criteria, all that kind of stuff. That's pretty cool stuff.
And of course, I got the big long search seminar. This is a lot of material. Everything you could ever possibly want to know about searching and sorting. Here's all the stuff that's covered. This sucker is over nine hours long. And if this doesn't cover it, I don't know what does.
I've also included a couple of other links to some other search videos that I got on my website in the TechHelp section.
All right, let's wrap this up.
So a multi-field search can absolutely find the correct record, but returning the record is only the first half of the job.
When a search checks several fields, the user needs to know which field or fields caused the record to appear. So what we learned today was how to make the matching fields easily identified with a little conditional formatting.
And remember, a database that explains its search results is a database that people will actually enjoy using.
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, we will walk through creating a multi-field customer search form in Microsoft Access that identifies which fields matched the user's search term. You will learn how to build a query that searches first name, last name, and notes fields, use conditional formatting with the InStr function to highlight matching text fields, and use VBA to load search results only after a search is entered. Quiz Q1. What problem does highlighting matching fields solve in a multi-field search form?
A. It shows users which fields caused each record to match
B. It automatically deletes duplicate records
C. It prevents users from editing search results
D. It sorts all records alphabetically
Q2. When searching for a term across FirstName, LastName, and Notes, where should the criteria be placed in the query grid to create an Or condition?
A. On the same criteria row for all fields
B. On separate criteria rows for each field
C. In the Sort row for each field
D. In the Show row for each field
Q3. What does placing criteria on the same row across multiple fields normally mean in an Access query?
A. At least one field must match
B. All fields must match
C. The fields are sorted together
D. The fields are hidden from the results
Q4. Which query criterion performs a wildcard search for text contained anywhere in a field?
A. Like "*" & Forms!CustomerSearchF!Search & "*"
B. = Forms!CustomerSearchF!Search
C. > Forms!CustomerSearchF!Search
D. Is Null
Q5. Why is it useful to use wildcard characters around the search text?
A. It finds records where the text appears anywhere in the field
B. It prevents duplicate records from appearing
C. It converts all text to uppercase
D. It searches only exact field values
Q6. Which function was used in conditional formatting to determine whether the search text appears in a field?
A. InStr
B. DLookup
C. DateDiff
D. Replace
Q7. What does an expression such as InStr([FirstName], [Search]) > 0 indicate?
A. The search text was found somewhere in FirstName
B. FirstName is blank
C. The search box contains a number
D. FirstName exactly equals the search text
Q8. Why are square brackets used around field and control names in the conditional formatting expression?
A. They ensure Access treats them as field or control references
B. They convert the values into text strings
C. They make the expression run faster
D. They force the field to be required
Q9. What type of conditional formatting rule is appropriate when using InStr in Access?
A. Expression Is
B. Value Is Equal To
C. Data Bar
D. Top 10 Values
Q10. Why might a search query appear to use an old search value while testing?
A. The user has not left the search text box, so its new value has not been saved yet
B. The query can only search one field at a time
C. Conditional formatting has disabled the query
D. The wildcard characters are not allowed in Access
Q11. How can a search form be configured to open with no records displayed?
A. Set its RecordSource to a query with a condition such as Where 1=0
B. Delete all records from the customer table
C. Set every field on the form to Hidden
D. Turn off the form header
Q12. What is the purpose of setting Me.RecordSource = "CustomerSearchQ" in the search box After Update event?
A. It changes the form to display the search query results
B. It saves the search text into the customer table
C. It applies a yellow background to every field
D. It closes the search form
Q13. Is a separate Me.Requery required after changing the form's RecordSource?
A. No, changing the RecordSource is enough to refresh the displayed records
B. Yes, Access cannot display records without Me.Requery
C. Yes, but only when searching the Notes field
D. No, because the form must be closed and reopened instead
Q14. What should a Clear Search button do in this type of search form?
A. Reset the RecordSource to the impossible query so no results are displayed
B. Delete the current search results from the table
C. Remove conditional formatting from the form
D. Clear the Notes field for every displayed record
Answers: 1-A; 2-B; 3-B; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-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 make a multi-field search form more informative by clearly identifying the field or fields that caused each record to appear in the search results.
A common search form lets the user type a word or phrase into one search box and then searches several fields at once. For example, a customer search might look through FirstName, LastName, Company, Address, and Notes. This is convenient because the user does not need to know exactly where the information is stored.
However, returning the correct records is only part of the job. If someone searches for a name such as Smith, the matching text might be in the customer's first name, last name, company name, address, or somewhere deep inside a long Notes field. If the user has to scan every visible field to figure out why a record appeared, the search form becomes harder to use.
The goal is to make the matching field stand out visually. We can do that with conditional formatting. When the search term appears in a particular field, that field will be highlighted so the user immediately knows why the record matched.
Before building this type of form, you should be comfortable with basic VBA, multi-field searches, wildcard searches using Like, query criteria, conditional formatting, Expression Is rules, the InStr function, and basic SQL. You should also understand the difference between And and Or conditions in a query. When criteria are placed across the same row in the query design grid, Access treats them as an And condition. When criteria are placed on separate rows, Access treats them as an Or condition.
I start with a customer list form that displays fields such as FirstName, LastName, and Notes. Rather than changing the existing customer list form, I make a copy and save it as a separate customer search form. This allows the regular customer list to remain unchanged while the new form becomes dedicated to searching.
I then add an unbound text box in the form header. This text box is used to hold the search term entered by the user. It should not have a Control Source because it is not connected to a table field. I name it something simple, such as Search. A label beside it can make its purpose clear to the user.
Next, I create a query based on the Customer table. The query includes only the fields needed by the form, such as CustomerID, FirstName, LastName, and Notes. It is generally a good idea not to include unnecessary fields in a query, especially in larger databases, network environments, or databases connected to SQL Server. Retrieving only the data you need can improve performance.
The query uses wildcard criteria based on the value entered in the Search text box. The criteria uses Like with asterisks before and after the search value so Access can find the search text anywhere inside the field. For example, if the user searches for Smith, the query can find Smith, Smithson, Goldsmith, or any other value containing that text.
Because the search needs to find records where the term appears in any of several fields, the criteria for FirstName, LastName, and Notes must be placed on separate rows in the query grid. This tells Access to return a record if the search term appears in FirstName or LastName or Notes.
One important point when testing the query is that the value in the search box may not be available to the query until the user leaves that control. If the cursor is still inside the Search text box, the most recently typed value may not yet be saved. This is not a problem once the form is automated because the search will run in the After Update event, which occurs after the value has been updated.
After confirming that the query returns the expected records, I set the Record Source of the customer search form to the new search query. The form can now display only those records that match the entered search term.
The next step is the key part of this technique: conditional formatting.
For each field that may contain the search term, such as FirstName, LastName, and Notes, I create a conditional formatting rule. The rule uses Expression Is because the standard Value Is options are not flexible enough for this type of test.
The expression uses the InStr function to determine whether the contents of the Search text box appear anywhere inside the current field. InStr returns a value greater than zero when it finds the search text. When that happens, the conditional formatting rule is true and Access applies the selected formatting, such as a yellow background color.
Each field needs its own rule because each expression must refer to that particular field. The FirstName control checks whether the search text appears in FirstName. The LastName control checks LastName. The Notes control checks Notes.
Square brackets should be used around field names and control names in these conditional formatting expressions. This helps Access correctly identify them as fields or controls rather than treating them as text or other values.
Once the formatting rules are in place, a search for Rich might highlight FirstName for Richard, LastName for someone with Rich in the surname, or Notes for a record where the word appears only within a longer note. This makes it immediately obvious why each result was returned.
To make the search form more efficient, I configure it so that it opens with no records displayed. This is particularly useful in a large database or when working over a network. There is no need to load every customer record until the user performs a search.
To do this, I set the initial Record Source of the form to a query that can never return records, such as a query with a condition that is always false. The form opens blank, but the search box remains available.
In the After Update event of the Search text box, I use VBA to change the form's Record Source back to the actual customer search query. Changing the Record Source automatically updates the form, so a separate Requery command is not necessary.
This means the user can type a search term and then press Tab or Enter. Once the Search box updates, the form displays the matching records and highlights the fields that contain the match.
I also add a small clear button next to the Search text box. Its purpose is to reset the form back to the blank, no-records state. The button uses VBA to restore the impossible Record Source used when the form initially opens. This gives users a quick way to clear the search results and begin another search.
This technique improves the usability of a multi-field search form significantly. Instead of forcing users to inspect every field in every result, the form shows them exactly where the search term was found. It is especially helpful when searching long Notes fields, addresses, company names, and other information that may not be immediately visible.
Also, in today's Extended Cut, we will expand this search technique to work with related tables. We will search both parent customer records and related child records, such as contacts, contact history, notes, or orders. We will also add a second search box so users can narrow the results further. This will include additional VBA and SQL techniques for building a more powerful parent-child search interface.
A multi-field search should do more than return the correct records. It should help the user understand why those records were returned. Conditional formatting provides a simple and effective way to make that information clear.
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 Building a multi-field customer search form
Creating an OR search query across fields
Using wildcard criteria with Like
Binding a search form to a query
Highlighting matched fields with conditional formatting
Using InStr in conditional formatting expressions
Showing no records when the search form opens
Loading results after updating the search box
Clearing search results with a button Article A multi-field search is useful when users need to find a word or phrase that may appear in several different fields, such as a customer's first name, last name, company, address, or notes. The search may return the correct records, but users can still be left wondering why a particular record appeared in the results.
For example, if a user searches for Smith, the match might be in the customer's last name, a company name, an address, or somewhere deep within a long notes field. Instead of forcing the user to scan every field in every result, you can use conditional formatting to highlight the specific fields that contain the search term.
Start with a continuous form or datasheet-style form that displays the customer records you want to search. Include the fields that users need to see in the results, such as FirstName, LastName, and Notes. You can include more fields if needed, but it is generally better to show only the fields that are useful for identifying the customer and understanding the search result.
Add an unbound text box in the form header for the user to enter the search term. Give this text box a meaningful name, such as Search. Because it is unbound, it is not connected to a field in a table. Its only purpose is to hold the value the user wants to search for.
Next, create a query that returns the records for the form. Include the fields displayed on the form, along with any key field needed to identify the records. The important part is the criteria.
For each field that should be searched, use a wildcard search based on the value in the Search text box. The search should look for records where the field contains the entered text, rather than requiring an exact match. In Access, this means using the Like operator with wildcard characters before and after the search value.
Make sure the criteria are arranged as an OR search, not an AND search. If you place criteria on the same row in the query design grid, Access treats them as AND conditions. That would require the search text to appear in every field at the same time, which is usually not what you want. Place the criteria on separate rows so a record is returned when the search term appears in any of the selected fields.
Set the form's Record Source to this search query. At this point, entering a value in the Search box and refreshing or requerying the form should display records where the text appears in at least one of the searched fields.
The next step is what makes the search results easier to understand. Apply conditional formatting to each field that can be searched.
For the FirstName control, create a new conditional formatting rule using Expression Is. The expression should check whether the value entered in the Search text box appears anywhere within the current record's FirstName value. The InStr function is useful for this because it returns a position number when it finds one string inside another. If it returns a value greater than zero, the search text was found.
Set the format for a successful match, such as a yellow background color. When the search term appears in the FirstName field, that field will now be highlighted.
Repeat the same process for LastName, Notes, and any other searchable fields. Each conditional formatting expression should reference the current field being formatted, while checking for the same value in the Search text box. The formatting can be identical for all fields, or you can use different colors if you want certain types of matches to stand out differently.
Once this is set up, a search for Rich might return several records. If Rich appears in a customer's first name, the FirstName field is highlighted. If it appears only in Notes, the Notes field is highlighted instead. If it appears in more than one field for the same record, each matching field is highlighted. This immediately explains why the record was included in the search results.
For a smoother user experience, the form should initially open without loading every record. This is especially helpful in larger databases or databases shared over a network. Instead of opening the form with the full search query active, configure its initial record source so that it returns no records. A common approach is to use a condition that can never be true.
Then, use the After Update event of the Search text box to switch the form's record source back to the actual search query after the user enters a search term and leaves the box. The code does not need to perform a complicated search itself. Its job is simply to tell the form to use the search query, which causes the query to run using the value currently in the Search box.
You can also add a small clear button next to the search box. When clicked, it should clear the search text and reset the form back to its no-records state. This gives users an obvious way to remove the current results and start a new search.
This technique works well because it separates two important jobs. The query determines which records match the search. Conditional formatting explains where the match occurred. Together, they create a search form that not only finds the right records, but also makes the results immediately understandable. Primary Topics
Access multi-field search, wildcard Like criteria, query OR conditions, conditional formatting expressions, InStr function, form RecordSource, VBA After Update event Secondary Topics
unbound search textbox, search form initialization, impossible SQL query, clearing search results, query performance by selecting only needed fields |
