Filter Combo
By Richard Rost
3 years ago
Filter Data in Combo Box or List Box as you Type
In this Microsoft Access tutorial, I'll guide you on how to filter data in a combo box or list box as you type. This skill is especially useful when you're trying to locate text that isn't at the start of the combo box and doesn't appear automatically due to the auto-expand property.
Sam from Jacksonville, Florida (a Platinum member) asks: I just watched your search-as-you-type video, and I think it's awesome! Thank you for that. Is there any way you can apply the same technique to a combo box or list box? I'm looking for a functionality where, as I type characters, it will filter the results in that box to show just the records that contain that text, regardless of where it appears in the field.
Members
There is no extended cut, but here's my database:
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
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Filter combo as you type, rowsource, requery combo, on change, text property, auto expand, filter combo box, filter list box
Subscribe to Filter Combo
Get notifications when this page is updated
Intro In this video, I will show you how to filter the results in a combo box or list box as you type in Microsoft Access, so only records that contain your typed text—anywhere in the field—appear in the list. We will talk about using the On Change event, updating the Row Source property with a custom SQL statement that includes wildcards, and handling double double quotes in VBA. I will also demonstrate how to set up an unbound text box above a list box for filtering, and discuss when to turn off the Auto Expand property for better results.Transcript Got a good one for you today. I'm going to teach you how to filter the data in a combo box or list box as you type in Microsoft Access.
I'm not just talking about the default way that combo boxes work when you start typing in the first couple of letters. That will drop you down to that spot. No, no, no, no, no. We're going to type in letters and filter the results that show up in the list to show only records that include that text. So here I started typing R-I, and it filtered it. So all of these guys down here include R-I and it's here. R-I, R-I, R-I. It's going to be cool. Here we go.
Today's question comes from Sam in Jacksonville, Florida, one of my platinum members. Sam says, I just watched your search as you type video and I think it's awesome. Thank you for that. You're welcome. Is there any way you can apply the same technique to a combo box or list box? I'm looking for functionality where as I type characters it will filter the results in that box to show just the records that contain that text regardless of where it appears in the field.
Yes, Sam, that is definitely possible. We can apply some filtering and we can change the SQL that's in there a little bit. It's going to require a little bit of code, but we can definitely do it. So this is going to be a developer video. What does that mean? That means you need to know a little bit of VBA.
If you've never done any VBA programming before, go watch this - about 20 minutes long - teaches you everything you need to know. It's free. It's on my website. It's also on my YouTube channel. You're also going to need to know a little bit of SQL - how to write an SQL statement. I'm going to show you what to do, but I recommend you learn this first to make it easier. Go watch this one, too.
You definitely need to know how to make a relational combo box, whether this combo box gets its data from another table or query. We're going to use a wildcard search using the like keyword to actually do the filtering in our SQL statement. So make sure you understand what this like thing is. And since we're dealing with text inside of a string, make sure you understand how to use double double quotes. This is a confusing one for a lot of people, so go watch this video.
Optionally, go watch my invoicing database. This is the database I'm going to use for this example, but you can use any database you want. It's got a combo box and it doesn't have to be mine. And if you want to watch the original video that Sam was referring to, go watch this search as you type. This is actually one of the most popular videos I've released on YouTube in the last month or two, so this is a really good video. If you haven't watched this one yet, go watch this.
Once again, these are all free videos. They're on my YouTube channel. They're on my website. Go watch any of those if you are questioning whether you know it or not, and then come on back and watch this one. This one's really good. You're going to enjoy this.
OK, so here I am in my TechHelp free template. This is a free database. You can download a copy off my website if you want to. Again, you'll find a link to that down below.
In here, I've got a customer form. Customers can have orders. The order form has a combo box where you can pick the customer. Now, if I type in the first couple of letters, like van, V, and it jumps you right to Van Halen. But what if I'm looking for Eddie? If I come back here and type in ED, there's no ED in here. But I want to still find Eddie. Now, yeah, I've only got a short list here so I could just scan down it. But what if you got a couple hundred records in here?
Well, there's a property in combo boxes. If you take a look, go to design view, open up the properties. On the data tab down here, you'll find this property called auto expand. This is the thing that happens when you type in VA, it jumps you down to Van Halen. By default, this is on. You can turn it off if you want to.
The only reason I would ever turn this off is if you've got thousands and thousands of records and your combo box is running really slow. Maybe you're pulling records over the internet, or over the network at least, and it's just taking forever. So if you turn auto expand off, it will speed up your combo box, but you don't get the auto expand, obviously. But auto expand doesn't look at anything but the beginning of the string. So what do we have to do to get this to work with any part of the string?
Well, we need our own event. We need an event that fires as the user types. For that, I'm going to go to events and I'm going to use the on change event. As you type in characters, the on change event fires each time. At the dot dot dot, we're going to go into the code builder.
Now, right in here is where you're going to put the code that's going to change the row source property of that combo box. What are we going to change it to? We're going to change it to what it is now, but we're going to add a where condition, an SQL where condition, to say where that first name, last name is like whatever the user typed in.
Now, let's go get the original SQL statement that's in here that the wizard provided for us. Go to data. Here's the row source right there. I'm going to zoom in so you can see the whole thing. Let's just copy. In fact, we can clean this up first. We don't need a lot of this. Since our data is only coming from one query, the customer LFQ, that's, for those of you who haven't watched the invoicing video, customer LFQ is a query that takes last name and first name and puts them together so you can see them in the combo box - last name, comma, first name. So Last, comma, Richard. Kirk, comma, James. That's all that query does.
But since it's only one query, we don't need to specify it here. So we can get rid of that. We don't need the brackets because we're good little children and we don't put spaces in our field names or our table names or our query names. So we get rid of that. So literally all we need is, we get rid of this.
All right, so our statement now is just select customer ID, last name first name, which is one field from the query, order by last name first name so they're sorted alphabetically. That's our SQL statement as it is now. So we're going to copy this.
All right, let's go back to our code editor. I put a button up here for it, it's right there. I always leave this open when I'm working, by the way, and just either switch back to the database this way and leave it behind that or just go down the taskbar and click on it. I almost never close this guy, unless I'm done.
So as the user's typing, this event's going to run. Now if I just want to reset the combo box to what it is right now without making any changes, I can say customer combo dot row source. That's the property that contains the row source that we just looked at. And I can put in here equals that that I just copied to my clipboard. That's the exact thing that's in there now. All that will do is just requery the combo box with the same set of data. If you want to try it, save it. Come back out here, close it, open it. And if I come in here now, if I type in V, nothing's really happening. But I'm just requerying it with the same set of data.
Now what I want to do is I want to add what the user typed in the box as a where condition. But I can't use the value of the box because as of right now, the value in that box is an ID. How do I get a hold of the text that the user is typing? Well, that's the text property of that box. This works with combo boxes, it works with text boxes. We'll do something different for list boxes in just a minute, because I know I told you this works with list boxes too. You have to add a text box to help out. But let's focus on the combo box for right now.
So right now, the user just typed in VA. So that VA is the text in that text box. OK. I'm going to hit escape for now. Just hit escape. Go back to where you were. Let's go back to our code. I'm going to clean this up one more thing. I like to put the select on one line, and then I like to put the where clause and the from and the order by on individual lines. It just makes it easier to read the SQL.
So select - these are just the line continuation characters - so select my fields from the query order by that. So after the from, I can put a where clause.
Where? What we're going to say is, where the last name first name is like, and then I need like star, you know, Joe or whatever in here. We've got double double quotes to deal with because this has to be inside quotes inside a string. So it's going to look like this: double double quotes to start an open quotes wild card, close the string and customer combo dot text to get the actual text the user typed in, open the string, close the wild cards, that's a wild card on the end of it, double double quotes, space before the order by (everyone always misses that space there), then close the quotes and continue the line.
I know that's a little confusing. That's why I wanted you to watch all those other videos first - the wild cards, the concatenation, the double double quotes. So this turns into a double double quote in the string followed by an asterisk, followed by van or VA or whatever you type in, followed by another asterisk for the closing wildcard, followed by another set of double quotes. So it's going to be like "van".
Then when we're all done with that, I want to drop down the box so as I'm typing, I can see that list refresh. So I'm going to say customer combo dot drop down. We're going to add this because if you don't, the list will refresh, the combo box will refresh, but you just won't see it in the hand. I want to actually see that list.
Save it. Let's give it a quick debug compile, just to make sure. OK, I'm good. Come back out here, orders, ready. I'm going to click in here. I'm going to type in V. And it goes right to Van Halen, but Van Halen was the only V in there. So let me hit escape a couple of times. Let's try over. Close that. Go back in. Let's drop this down and type in - let's go R. I. Look at that.
Brian has R.I. right there. Paris has R.I. right there. There's Richard R.I. See all these contain R.I. And that's like seeing the list. I still got to pick the right value.
Close it. Let's try this. Come in here. Type in R.I. Let's pick Miles O'Brien. Hit tab. Now, say I go away, come back to it, come in here, type it again, R.I. Wait a second. Give me Riker William. Why is that? Well, once in a while, sometimes that auto expand is trying to be your friend, and it auto expanded that into Riker. It doesn't work every time, but once in a while it does.
To get rid of that, you can turn auto expand off, and it's going to stop trying to help you. But our filter is doing most of the work. That's all you really need. So I'm going to hit escape again. Let's go into design view. Go to this guy. Turn auto expand off because we really don't need it when we've got this feature.
Come back in here. Now if I go R.I. I get my Riker. This usually happens, the bug usually happens if you pick somebody and then you go back in and try to pick it again. R.I. See, now it's working properly. We're doing our own auto expand in code. The auto expand will literally put William Riker in the box as the text, and that's why it messes up the code. So you got to turn auto expand off if you want this to work like this. But I like this a lot better.
There you go. That's how you do it. Stuff! Come down here, type in J, there's all the J's. J.A. There you go. See? I think that's cool. I like that.
OK, so that's how you do it with a combo box. Now, what about a list box? Let's turn this guy into a list box just for the sake of class here. Let's take this, I'm going to move it over here. Let's slide it over here. We get rid of the label. Drop this down and make it a list box. Right click - change to list box. So here's my list box to pick the customer.
Now save it, close it, open it. Here's my list box. With a list box, unfortunately, you can't type. You can type characters, but nothing's going to happen. It'll jump to that spot like a G. If I hit G, it'll go there. I'll hit O, it'll go to O'Brien. But we can't do our filter trick unless we add a text box above it.
In fact, let me save this for the gold members. Let me undo that stuff. Undo. Oh, I can't undo it, can I? All right, I'll change this guy back to a combo box. Gold members, I'll put it right over here for you. I'll copy this and paste it, and it goes in the subform. So I'll move that over here, and then we'll change this back to a list box.
Let's call this guy, let's call it customer list. And it's the customer list. What do we got? An invulnerable control? Oh, control source. I thought that was the name. My bad. I'm going to leave this in the video because if I make this mistake, I make this mistake all the time. I forgot that I'm on the data tab, and I'm usually used to being all where the top one is the name. But I changed the control source instead of the name. The control source for this has to stay customer ID. But on the all tab, all the way to the top, we're going to change it from combo 14 to customer list. I make that mistake a lot. So I'm leaving it in here so you guys can see it.
OK. So here's the customer list. We're going to add a text box above it to be its filter. I'll just grab a text box over here. We'll copy description, copy, paste, slide it up top, like there. Delete the label. We're going to unbind this guy, so get rid of description from there. Now it's an unbound text box. It's just where we're going to type in our filter.
The name of this will be customer list filter. So we got the customer list and the customer list filter. So in the customer list filter is where I will put the on change event, and it's going to be just like this one right here. We don't need to drop down because you don't drop down a list box. Put that in there. This is going to change to customer list dot row source equals same stuff, and it's going to be customer list filter dot text.
Save it. Back over here. Let's close it, open it up. Come over here. Type in R-I. Look at that. L-A-F. OK. I. That's how you do it with a list box. Put a little filter box above it.
Notice when you pick a value over here, this one changes too. Why? Because this field and this field are both bound to the same field in the table. I should technically say this control and this control are bound to the same field in the table. They're both bound to the customer ID, so if you change this one, the other one will change too.
If you like learning this stuff and you enjoy learning with me, I've got lots of developer lessons on my website - 44 of them by now. So there's tons and tons of material and I teach stuff in the order that you should learn it in based on my experience. So come and check it out. I'll put a link down below. Also, if you like this finding, searching, sorting stuff, check out my search seminar. It's several hours long, I cover all kinds of stuff - searching out all kinds of different fields, doing ranges, dates, you name it. It's covered in this. This is everything you ever need to know about searching and sorting and all that good stuff. I'll put a link to this down below.
So there you have it, folks. There's your TechHelp video for today. Now you know how to filter a combo box or a list box as you type. Hope you learned something today. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What main functionality is being added to combo boxes or list boxes in Microsoft Access in this lesson?
A. Allowing selection of multiple values simultaneously
B. Changing the color scheme based on the user's input
C. Filtering the list to show only records that contain the typed text anywhere in the field
D. Auto-saving the selected value to a backup table
Q2. How does the default auto expand property of a combo box work in Access?
A. It filters the list for any record with matching text anywhere in the field
B. It jumps to the first record that starts with the typed letters
C. It sorts the list alphabetically in real-time
D. It lets users select multiple options by typing their initials
Q3. Why might you want to turn off auto expand when implementing this filter-as-you-type feature?
A. It makes the database incompatible with SQL
B. Auto expand only looks at the start of strings and can conflict with the custom filtering logic
C. It disables the row source property
D. Auto expand always returns the first record regardless of input
Q4. Which event is used to trigger filtering as the user types in the combo box?
A. After Update event
B. On Click event
C. On Change event
D. On Got Focus event
Q5. What needs to be changed in the combo box code to filter results based on user input?
A. The Row Source property should be updated with a WHERE clause using the LIKE operator and wildcards
B. The Auto Expand property must be set to True
C. The Visible property must be toggled off and on
D. The table must be sorted by primary key
Q6. If you want to filter records to include text typed by the user, which SQL keyword is essential in your WHERE clause?
A. BETWEEN
B. SUM
C. LIKE
D. HAVING
Q7. When filtering in a combo box, why is the .Text property needed instead of the .Value property?
A. .Value returns the underlying text, while .Text returns the bound field
B. .Text holds the current characters the user is typing before selection is made
C. .Value is required for list boxes, not combo boxes
D. .Text is read-only and cannot be used
Q8. When implementing filter-as-you-type in a list box, what additional control is required?
A. A label above the list box
B. A checkbox beside each item
C. An unbound text box for inputting the filter text
D. A command button to clear the results
Q9. Which of the following accurately describes how to construct a WHERE clause for filtering by user input?
A. WHERE LastNameFirstName AND [UserInput]
B. WHERE LastNameFirstName = '*UserInput*'
C. WHERE LastNameFirstName LIKE '*' & [UserInput] & '*'
D. WHERE [UserInput] LIKE LastNameFirstName
Q10. What is the reason for using double double quotes within the VBA string when building the SQL statement?
A. Access ignores single quotes in SQL statements
B. It lets you concatenate two different fields together
C. It is required to correctly represent quotes inside a VBA string literal
D. It enables auto expand by default
Q11. What is the effect of calling the DropDown method on the combo box after filtering?
A. It deletes all other controls from the form
B. It automatically selects the first record
C. It displays the filtered list to the user as they type
D. It refreshes the entire form
Q12. For filtering a list box, where should the filtering event handler (VBA code) be placed?
A. In the list box's On Change event
B. In the form's On Load event
C. In the unbound text box's On Change event
D. In the table's After Update event
Q13. If both a combo box and a list box are bound to the same table field, what happens when you change the selection in one?
A. The other control will update to match because they share the same control source
B. The database will throw an error
C. Only one of them will save values to the table
D. No changes will occur in the other control
Q14. Why can you not filter a list box directly as you type in it, unlike a combo box?
A. List boxes accept only date inputs
B. List boxes do not have a user-editable text area for filtering
C. List boxes do not have an Auto Expand property
D. List boxes are read-only by default
Q15. What is the purpose of using the LIKE keyword with wildcards when filtering?
A. To ensure only exact matches are shown
B. To allow partial and flexible matching of the user input anywhere in the field values
C. To sort the results alphabetically
D. To add totals to the row source
Answers: 1-C; 2-B; 3-B; 4-C; 5-A; 6-C; 7-B; 8-C; 9-C; 10-C; 11-C; 12-C; 13-A; 14-B; 15-B
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 filter data in a combo box or list box as you type in Microsoft Access. I am not referring to the standard behavior of combo boxes, where typing the first few letters jumps you to that position in the list. What we are doing instead is creating dynamic filtering, so as you type any characters, the list updates in real time to display only records that contain that text sequence, regardless of where it appears in the field. For example, typing "R-I" will show only those records, even if R-I appears in the middle or the end of the field.
This lesson was inspired by a question from a student who recently watched my "search as you type" video and wanted to know if the same functionality could be added to a combo box or list box. Specifically, they were looking for a way to filter records in the dropdown as they typed, showing only those that contained their entered text anywhere in the field.
The answer is yes, you can definitely accomplish this. It requires a bit of VBA coding and a working knowledge of SQL statements inside Access, but it is very doable. Before we proceed, it helps if you have some experience with VBA programming and SQL. If you are new to either, I recommend first watching my introductory VBA programming and SQL videos, which are available for free on my website and YouTube channel. You should also be comfortable working with relational combo boxes and know how to use the LIKE keyword in SQL for wildcard filtering. Since this process involves manipulating strings in code, understanding how to format double quotes inside strings will also help a lot.
To demonstrate this tutorial, I use my free TechHelp template database, which you can download from my website. In this sample, there is a customer order form with a combo box to pick a customer. Normally, if you type the first letters of a customer name, Access jumps down the list to the first matching entry. This is controlled by the "Auto Expand" property of the combo box. Auto Expand works only for the beginning of the string, so if you want to find, say, "Eddie" by typing "ED", but Eddie is not at the start, the default search will not help you.
If you manage thousands of records and your combo box loads slowly, you might want to turn off Auto Expand, but in this lesson, we are overriding the default anyway. Our goal is to filter the dropdown as you type, based on any portion of the text.
To achieve this, we need to use an event that triggers every time the user types in the combo box. The "On Change" event serves this purpose. In the event handler, we will modify the combo box's row source property using a SQL statement that adds a WHERE clause, applying a LIKE condition based on the user's input.
First, take note of the current SQL statement in the combo box's row source. In many cases, the combo box is set to display a value merged from another query or table, such as concatenating last and first names. You only need the essential part of this query, selecting the relevant fields and sorting them, without unnecessary brackets or extraneous table references if your field and table names use no spaces or special characters.
Next, in the VBA code for the On Change event, update the row source property dynamically. Use a WHERE clause that leverages the LIKE keyword with wildcard asterisks, and reference the combo box's Text property to capture what the user is typing. Be careful with the string formatting so the SQL statement is built correctly, especially when placing quotation marks and wildcards.
After updating the row source, use the combo box's DropDown method so the list visibly refreshes as the user types, letting them see the filtered results right away. Test your new setup by typing in sequences of letters; as you do, the combo box will instantly filter the records to only those that contain those letters in any position.
You may notice that occasionally, Auto Expand tries to assert itself, causing some confusion, especially if you select the same record twice. To prevent this, turn off the Auto Expand property in your combo box's settings. With this property off, your custom filtering logic will work smoothly and consistently, giving you a reliable search-as-you-type filter for your combo box.
Now, let's apply the same concept to a list box. Unlike combo boxes, list boxes do not let you type directly for filtering, only for jumping to the first matching entry. To filter a list box as you type, add a separate unbound text box above it. Configure the On Change event for this text box, and use almost the same code as your combo box filter, but reference the list box and the filter text box accordingly. As you type in the filter box, the list box's Row Source updates to display only records matching your input.
One thing to be aware of: if you have both a combo box and list box, and they are bound to the same field in your data, selecting a value in one will update the current record selection in the other, since both controls are connected to the same data source.
If you enjoy learning Access development topics like this, you will find many more lessons and seminars on my website, covering everything from searching, sorting, invoicing, and much more. These resources are organized in a logical progression to build your knowledge step by step.
So now you know how to filter a combo box or list box as you type in Microsoft Access. If you want to see this whole process demonstrated with step-by-step instructions, you will find a complete video tutorial on my website at the link below.
Live long and prosper, my friends.Topic List Filtering combo box items as you type using VBA
Filtering list box items as you type using VBA
Changing the Row Source property in VBA
Using the On Change event in combo boxes
Building SQL WHERE clauses with LIKE wildcards
Accessing the .Text property of a combo box
Handling double double quotes in SQL strings
Disabling Auto Expand in combo boxes
Adding an unbound text box to filter a list box
Synchronizing controls bound to the same field |
