Search As You Type
By Richard Rost
3 years ago
Create a Search-As-You-Type Box in MS Access
In this Microsoft Access tutorial I'm going to teach you how to create a search-as-you-type box for your Microsoft Access forms.
Benjamin from Euclid, Ohio (a Platinum Member) asks: Is there a way to create a search box in my customer list form that allows me to quickly find a customer by their first or last name? Ideally, I'd like to start typing the first few characters and have it immediately direct me to that customer, similar to how a Google search begins showing results before you've finished typing. Is such a feature possible in Access?
Members
Members will learn how to create a single text box to search on any field and have the results automatically update every time they press a key. Will make a button to clear the search box.
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!
Space Problem
Prerequisites
Links
Recommended Courses
Update
- If your combo box isn't automatically selecting a value after you type a few characters in, make sure the AutoExpand property is set to Yes. This is the default setting now, but in some older versions of Access it was No. You can set this to No if you want to use a combo box and have tons of records (or a slow network connection) and you want to speed up your box.
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, search as you type ms, search while typing, instant search results, search textbox, search-as-you-type
Intro In this video, I will show you how to create a search-as-you-type combo box for your Microsoft Access forms, allowing users to quickly locate customers by typing the first few characters of their first or last name. We'll look at common challenges with duplicate names and unsorted results, and I'll demonstrate how to build an aggregate query for a cleaner search list and tweak the combo box's properties and macros to make the search process smoother. This solution does not require programming and is suitable for users with an intermediate understanding of Microsoft Access.Transcript Welcome to another TechHelp video brought to you by Access Learning Zone. I am your instructor Richard Rost.
In today's video, we're going to do a cool search-as-you-type box for your Microsoft Access Forms.
Today's question comes from Benjamin in Euclid, Ohio, one of my Platinum members. Benjamin says, Is there a way to create a search box in my customer list form that allows me to quickly find a customer by their first or last name? Ideally, I'd like to start typing the first few characters and have it immediately direct me to that customer, similar to how Google search begins by showing results before you finish typing. Is such a feature possible in Access?
Yes, Benjamin, it's possible. Of course, there are lots of different ways to do this. First, I'm going to show you the easy way, and then later on in the extended cut, I'm going to show you the more advanced way.
A little while back, I did a video called Find Record, where I show you how to make a Find a Record combo box using one of the built-in wizards in Access. This does about 90 percent of what you need, but it's not perfect. It needs a few tweaks to do exactly what you want. That's what we're going to do in today's video. We're going to take this and make it sexy.
If you have not yet watched this video, go watch it now. Also, you should know how to make aggregate queries. You should know what null values are. We're going to be using the dreaded double-double quotes. Go watch this video. If you don't know any of these things, these are free. They are on my YouTube channel. They're on my website. Go watch them now and then come on back.
Now, as I said, this is going to be an expert-level video, which I define to mean more than beginner, but not quite developer. I'm going to show you how to do what we need to do today without any programming. We are going to work with a little bit of macros. This is a macro that the wizard is going to create for us, and it's not that difficult to just adjust it a little bit.
Here I am in my TechHelp free template. This is a free database. You can download it on my website if you want a copy. In this database, I've got a customer list. Wouldn't it be nice to just be able to type in a few characters and jump right to Jordy LaForge, for example, based on either his first name or his last name?
Obviously, with only 29 records, I could just sort this, right-click sort, and I could scroll down and easily find Jordy. But if I've got 29,000 records in here, it's not that easy.
If you watched my Find Record video, you know how easy it is to make a Find Record combo box. Let's do that real quick first, just as a brief review.
I'm going to slide all this stuff down. I'm going to open up form design here and grab a combo box. There it is. Drop it up here. We're going to find a record. Next. What field do you want? Now, even if you only want first name, the wizard is going to bring in the ID too, which causes a problem. We'll talk about that in a little bit.
Next. Look, see, hit the key column. See right there. Next. What label do you want? Doesn't matter. I'm going to delete it. Hit finish. There we go. There's our little search box. We'll put it right above first name. Save it. Close it. Open it back up again.
Now, if I drop this box down, you'll see there's a list of items. Or if I just start typing here, this is what you want: search as you type. If I type in GE, there's Jordy right there. Now all I have to do is hit Enter or Tab and it takes you right to Jordy. In essence, that's a search as you type.
If I click in here and I type in I Tab, boom, there you go. It searches in this box for what you're looking for. GE, there's John Luke. Tab, then it takes you right to John Luke. Mission accomplished.
But what if I've got five James in here? Let me just copy this. We'll just make a bunch of James. There's another James. There's another James. There's another James. Let me put one more at the top here. Now we've got a bunch of Jameses.
If I close this, open it back up again... Now I can see them all right there. If I come in here and look in this list, there's a James there, there's a James down here, James down there. If I just type in James Tab, it brings me to... Okay. This list is sorted, that's fine. But as you can see, each one of these Jameses represents a different record, and you can't really tell which one is which. This is just a mess. This list isn't sorted or any of that stuff.
So what I want to do is I want to make a sorted list for this search box. I only want to see one of each record. I want to group them together using an aggregate query. Let's do that next.
The wizard, as it is, builds a nice little simple combo box. But I want to make it better. So let's delete this guy. Go away. Close this and save it.
Let's make an aggregate query to make a grouped list of customers based on their first name.
Create query design, best of a table, first name. That's all you need, just the first name, totals, group by, and then we're going to sort it. I also want to see blank values, so I'm going to put in here is not null for my criteria.
When I run this now, look at that. I get a nice sorted list of all the customers' first names, and there's only one James showing up there. Perfect. Let's save this guy, Control + S. Let's save this as First Name Search Q.
Now let's make that combo box look for this guy. The problem with the combo box is that if you go to create it in here with the wizard... Let's start over. Find a record. This guy unfortunately doesn't let you pick another query for your data. So let's go ahead and just rebuild this real fast. It takes two seconds. Next. Finish.
Now how long did that take? Not so long. All right. Let's open this guy up. Take a look at its property. First, I don't like combo 19. Let's call this First Name Search Combo.
Now let's go over here to the Data tab. The Data tab, the Row Source, tells you where it's getting its data from. Control Source is what it's bound to. This guy isn't bound to any field. It's unbound. The other guys are all bound to fields in the table underneath: first name, last name, and so on. This guy isn't bound. It just sits there. You pick a piece of data from here and it doesn't go anywhere. It just sits in that box. That's what unbound means.
Now let's take a look at this Row Source. Click on here. I'm going to zoom in, Shift F2. This is what the wizard gives us. It's a SELECT statement. For those of you who aren't familiar with SQL, you can go watch my Quick SQL With Access video. It teaches you all the basics. But this basically says, give me the Customer ID, comma, the First Name from the Customer table. Now, notice there's two fields there. So the wizard is giving me a specific record: Customer ID and First Name.
But I want to change what I'm getting. I want to get that list from the query I just made. So we're going to change this. Get rid of all this stuff here. Leave the SELECT. We're going to say SELECT FirstName FROM First Name Search Q. I want just the first name from First Name Search Q.
A couple of other things real quick. Go over to Format. This guy has two columns in it because, remember, the wizard set it up with ID and first name. Set the column count to one. The column width is going to be just one inch. We've got one column in here.
Save it. Why didn't I just build a new combo box? Because the magic happens in this embedded macro. Go to Event here. You'll see there's an embedded macro. We'll get into this in just a second. I didn't want to have to recreate this from scratch. So it's easier just to modify this combo box.
Save that. Close it. Open it up. Take a look at what's in the box. Look at that. This sorted list of just the single first name—one James. Looking good so far.
But let's pick one. James. Type mismatch. What's going on now? Well, the macro is expecting an ID, not a string. We've got a little more work to do. Hit OK. Stop macro. Go back to Design View. Open up this guy's properties. Go into Events. Open up the embedded macro. Click in here and hit the ... button. I don't do a lot of work with macros, but once in a while they're OK.
So, what does this macro command do? This is the Search for Record. These two: Object Type and Object Name—don't worry about those. It says find the first record. The Where Condition is where Customer ID equals, and this is just a fancy way to say whatever the active control is. They do that for the wizard to make it generic, so it works with whatever control you were on.
We're going to change this, though. We're not going to use the Customer ID. We're going to say where FirstName equals our First Name Search Combo box. Change this to FirstName, and then we can get rid of this stuff. FirstName equals—get rid of all this stuff here.
Now, here's where our double double quotes come in. It's going to be First Name Search Combo, but it's got to be inside double quotes because it's a string. So it's going to be: "", [FirstName Search Combo], "". Make sure you have that little equal sign there. Again, the wizard built that for us.
Save it. Close it. Close it. Save it. You've got to save the macro separate from the form. That's kind of weird.
Now open it up. Drop it down. Pick John Luke. Boom. Puts you right on John Luke. Same thing. Come in here and type in James. It'll put you at the first James.
Now, what if your list isn't necessarily sorted by that? Let's say here, we're sorted by ID or by date or whatever. I pick Catherine. There's only one Cat and that's not a problem. If I put three Jameses together... Did I? Let me move you. Let's make this a Xavier, and I'm going to move James down here.
Close it. I open it now. If I do a James, it takes me to that James over there, but I don't see that there's more Jameses out there. Wouldn't it be nice if I searched for that based on that box? If it sorted that list for me and put me at the first one, that would be pretty cool.
Let's do that. Design View. Go back into our combo box. Go back to the Events after update. The next action down here—we're going to use Set Order By. You might think it would be sort or order by, but no, it's Set Order By. What are we ordering by? FirstName. Don't worry about control name.
Save it. Close it. Close it. Save it.
Ready? Just to show the real list, come in here. James. Tab. And what happened? Well, it sorted it, but setting the sort order put you at the top again. So what we have to do is do the sort first, then do the search.
How do we do that? Come back in here. Design View. Go back to the combo box. Go back to the events. Click on the little green arrow right there. Move it up top. So what will happen is, it will sort by FirstName first, then do the search, and hopefully leave us where we need to be.
Ready? Save it. Close it. Close it. Save it. Close it.
Customer. Here we go. We're all sorted here. Let's sort by a different field just to make sure it works. We're sorted by LastName now. Come over here. Drop it down. Let's pick James. Boom. Look at that. It sorted by FirstName and put us right on the first one.
Come in here and type in something else. Anyone else we got in here? Jordy. GEO. Tab. There you go.
There's your basic search as you type. You're searching in here. The records will come up. I see there's Richard. Tab. Boom. Puts you right on me.
So that was pretty cool. But if you want to learn something that's even cooler, the cooler solution—in the extended cut for the members, I'm going to show you how to do this with a text box. Literally, as we type, the records down here will be refreshed. It will work with any field you want.
With the one that we built here, if you want to do this with LastName and then with State, you have to build separate boxes for each one of those, which isn't a bad thing. But the one I'm going to show you how to build in the extended cut: if I type in here, A, L, see—it works. I've got it set to work on FirstName, LastName, and the State column. If I back up and I go T, A, see: it finds Tosh, it finds Data, it finds Naota. If I type in F, L, it finds all the Floridas.
We'll make a clear button. It will truly update the records whenever you press a key. We'll use VB inside of macros, because I'm not a huge macro fan. They've got their place, but I prefer VB myself. That's all covered in the extended cut for the members. Silver members and up get access to all of my extended cut videos.
If you really want to take your Access databases to the utmost stratospheric levels of searching capabilities, I have this thing that I built called the Search Seminar, one of my more popular seminars where I cover pretty much all there is to know about searching and sorting inside of Access. So, check that out as well.
That 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.Quiz Q1. What is the main goal of the tutorial presented in the video?
A. To create a search-as-you-type box for Access forms
B. To teach how to create tables in Microsoft Access
C. To explain how to use VBA code for automation
D. To demonstrate exporting data from Access to Excel
Q2. What is the primary benefit of a search-as-you-type box in a large customer list?
A. It hides duplicate records
B. It helps to quickly find a customer as you type a name
C. It exports records automatically
D. It automatically deletes old records
Q3. Which control is initially used to implement the search feature in the form?
A. Text box
B. List box
C. Combo box
D. Command button
Q4. What issue occurs when there are multiple customers with the same first name using the default combo box?
A. The combo box stops working
B. It only shows the last entered record
C. It is unclear which record is which because all are listed without distinction
D. It merges the duplicate records into one
Q5. Why is an aggregate query used in the improved solution?
A. To count all records in the table
B. To group customers by unique first names and eliminate duplicates
C. To display records in a random order
D. To merge customer IDs and names
Q6. After building an aggregate query for first names, what property of the combo box must be updated?
A. Control Source
B. Row Source
C. Default Value
D. Tab Order
Q7. What does it mean that the search combo box is "unbound"?
A. The combo box is linked to multiple fields
B. The combo box is not connected to any table or field as a control source
C. The combo box has no formatting
D. The combo box is only used for dates
Q8. What error appears when trying to use the macro before updating it to use first names instead of Customer IDs?
A. Record Not Found
B. Missing Query Error
C. Type Mismatch
D. Syntax Error
Q9. What needs to be changed in the macro to search by first name instead of by Customer ID?
A. Change the search field to LastName
B. Set the criteria to be where FirstName equals the combo box value
C. Add a second combo box for IDs
D. Remove the WHERE clause completely
Q10. Why are double double quotes ("", [ControlName], "") used in the macro condition?
A. They allow searching for numeric values
B. They define a string comparison in the macro condition
C. They protect against SQL injection
D. They trigger the macro to refresh the form
Q11. What does the Set Order By macro action do in the form?
A. It activates a record filter
B. It sets the sorting order of records by the specified field
C. It saves the current record
D. It counts the total number of records
Q12. If the Set Order By action is placed after the macro searches for a record, what is the outcome?
A. The records will be sorted before the search
B. The records will be sorted after the search, potentially moving the selected record out of view
C. The records will not be sorted at all
D. The search will only work for unique values
Q13. According to the video, what is a limitation of using a combo box for this search-as-you-type technique?
A. It cannot be sorted alphabetically
B. You must create separate combo boxes for every field you want to search
C. It only works with date fields
D. It cannot display more than ten records
Q14. In the extended cut tutorial for members, what added capability is demonstrated?
A. Using a combo box to filter by customer balance
B. Using a text box to instantly filter records as you type in any field, not just first name
C. Exporting records to an external database
D. Sending emails to all customers in the list
Q15. Why is it necessary to save both the macro and the form while making changes?
A. Macros and forms are separate objects and do not auto-save together
B. To update the data source of the combo box
C. To reset the record counter
D. To rebuild the entire form structure
Answers: 1-A; 2-B; 3-C; 4-C; 5-B; 6-B; 7-B; 8-C; 9-B; 10-B; 11-B; 12-B; 13-B; 14-B; 15-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 TechHelp tutorial from Access Learning Zone covers how to build a search-as-you-type feature for Microsoft Access forms. I'm Richard Rost, and today I will walk you through the process of creating a responsive search box on your customer list form that helps you quickly locate a customer by typing just the first few characters of their first or last name.
Many people ask whether it's possible to make a search box in Access that functions like Google, where results update as you type. The answer is yes. There are various ways to approach this, but in this lesson, I'll begin with the straightforward method, and for those interested in more advanced techniques, I'll discuss enhancements in the extended cut.
Previously, I created a tutorial about making a Find Record combo box using Access's built-in wizard. That method handles most of what you need for a basic search box but falls short in a few areas. Today, I'll build on that foundation and address those missing features, creating a more user-friendly and streamlined search tool.
To get the most out of this lesson, you should be comfortable with making aggregate queries and working with null values. Familiarity with the double-double quotes in Access and basic SQL will also help. I encourage you to review my free videos on these topics if you need a refresher.
This is what I call an expert-level tutorial. It's suitable for users who are beyond the beginner stage but are not yet full-fledged developers. We will not use any programming code, just a bit of macro adjustment. The wizard will produce the basic macro for us, and we will make the necessary changes.
For demonstration, I'm using my free TechHelp template database, which you can download from my website. Inside, there's a customer list, and our goal is to jump directly to a customer like Jordy LaForge by entering a few characters of his first or last name. In small databases, you could sort and scroll through the list without much trouble, but if you have tens of thousands of records, you need a more efficient solution.
The Find Record combo box tutorial shows how to add a combo box to your form using the wizard. This allows you to start typing a name, and it automatically finds matching records. The box even moves to the correct record after you hit Tab or Enter. This provides a basic search-as-you-type experience.
However, when you have multiple records with identical names, such as several customers named James, the default combo box setup presents some issues. It does not group or sort results in a helpful way, and the list can become cluttered and confusing, making it hard to tell one James from another.
To resolve this, we can use an aggregate query to produce a list of unique first names from our customers table, sorted alphabetically and excluding null values. After saving this query, we can make the combo box's data source point to our new query instead of the original customer table. We remove unnecessary fields (like Customer ID) from the Row Source and set up the combo box with just one column—the first name.
The combo box created by the wizard remains unbound, meaning selecting something in it does not change any field in the underlying table. Instead, it only serves as a lookup tool.
Next, we need to address an issue that arises when trying to search: the macro expects a Customer ID, but our query now returns only first names as text, not IDs. To fix this, we edit the embedded macro in the combo box and adjust the WHERE condition so that it looks for a record where the first name equals the text in the combo box. This is where the double-double quotes are needed to enclose the string value properly.
After making these changes and saving the form and macro, the search box works as intended. When you select or type in a name like James, it jumps directly to the first matching record.
But what if your form is sorted by another field, and you want the records to reorder automatically to put the matching record at the top? Access does not use a "Sort" macro action, but rather a "Set Order By" action. Adding this to the macro will sort the form by first name after a search. The actions in the macro must be ordered correctly: set the sort first, and then perform the search, so the user always lands on the intended record.
Once everything is set up, the combo box allows you to type in the first few letters of a name, provides you with unique, sorted results, and moves you to the relevant customer record in your list.
If you are interested in more advanced functionality, in the Extended Cut I show how to build a search feature using a text box that actually refreshes the underlying records as you type. This method works for searching across several fields — for example, first name, last name, or state — using only one search box. With this version, as you type different characters, the records are filtered in real-time. We even add a clear button. This approach is more dynamic and uses VBA to provide a professional, robust search tool that updates instantly with each keystroke.
If you want to take your Access searching skills even further, I have a Search Seminar available that covers just about everything you would want to know about searching and sorting inside Access. It's one of my most popular seminars.
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 a search-as-you-type combo box in Access forms
Building a Find Record combo box using the wizard
Handling duplicate first names in the search list
Creating an aggregate query to group and sort first names
Filtering out null values in queries
Setting up the Row Source for the combo box with a query
Configuring combo box properties for a single column
Modifying the embedded macro for string-based searching
Using double double quotes for string comparisons in macros
Resolving type mismatch errors in macro-based searches
Using Set Order By macro action to sort form records
Reordering macro actions to ensure correct search behavior
Testing search functionality with sorted and unsorted lists |
