Find Record Combo
By Richard Rost
4 years ago
Find Record Combo Box in Microsoft Access
In this Microsoft Access tutorial, I will show you how to find a record based on the value you select in a combo box.
Erin from Springfield, Missouri (a Platinum Member) asks: I love your Search Form 2.0 video. I have a great search form that works well because of you. Thank you. I'm trying to simplify it, however. Is there any way that we can just pick from a list of what's already in that field instead of having to type it in? For example, can I pick from a list of last names to show customers with that last name?
Members
Members will learn how to use multiple filter combo boxes together on the same form and either an AND or and OR condition. We'll also make a button to clear the filters.
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!
Recommended Course
Notes
- If you don't see the 3rd option to find a value when running the wizard, that's because your form's Record Source is not a table. If your form is based on a query, you don't get this option. Watch the video at time index 3:08 for details.
Links
 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, find, combo box, value, filter, filteron, group by, having, tempvars, function event handler, and/or, clear button, Move to specific record from Combo Box selection, How do I find record in Access form, How do I bring up a record based on a combobox
Intro In this video, I will show you how to create a search combo box in Microsoft Access to quickly find and filter records on your forms. You will learn how to build a combo box that allows users to select values from a list, such as last names or states, and have the form display matching records without typing. I'll demonstrate setting up the combo box using the wizard, sorting its values, and using simple Visual Basic (VB) code to filter records based on your selection. This is a useful technique for improving navigation and search functionality in your Access databases.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.
In today's video, I'm going to show you how to create a search combo box. We're going to use a combo box to find a record in your form based on the value that you select in the combo box.
Today's question comes from Aaron in Springfield, Missouri, one of my platinum members. Aaron says, I love your search form 2.0 video. I have a great search form that works well. Thank you. You're welcome. I'm trying to simplify it, however. Is there any way that we can just pick from a list of what's already in that field instead of having to type it in? For example, can I pick from a list of last names to show customers with that last name?
Well yes, Aaron, this is actually quite easy to do and I'm going to show you how in just a few minutes. All we have to do is use the combo box wizard to find a record based on a value selected in the combo box.
Now for the rest of you, if you haven't watched my search form 2.0 video, go watch it. It's an improvement on my original search form video, which was one of my most popular videos on YouTube. I redid it a little while ago.
Basically, I just create a bunch of text boxes across the top of the columns on a continuous form, and when you type something in like New York or a date range, it will filter the result down here to show you what you're looking for. But what Aaron is saying is she doesn't want to have to type in Riker, for example. She just wants to be able to drop a combo box down here and pick Riker and then have it jump right to that record. So let me show you how to do that.
Here I am in my TechHelp free template. This is a free download on my website if you want to go grab a copy, and I explain how that template is built in my blank template video. Go watch that if you want to see how this whole thing is built. Again, this is a free video. You'll find links down below. You can click on it and watch this.
Now in my database here, I've got a customer form. That's a single form. You can see one record at a time, or I've got a customer list that shows you all the customers. You can use this technique that I'm about to show you on either a single form or a continuous form.
Personally, I think it works better on a continuous form because you want to pull up a list of all your customers. Now, I've only got six customers here. But if I had 6,000, it might be easier if I could just click a combo box up top here and drop down and pick Picard, and it drops me right on Picard's record. Then I can double click here and go to his single record.
So let's go to design view. I don't need this property sheet right now. Let's close that. I'm going to make some room here to put this in the form header. I'm going to grab the top of the detail section, slide it down. We'll just grab all of these labels and then slide them down too.
I'm going to put a combo box right here above last name that I can use to find a record based on the last name. Go to your form design, go to the control box here, find the combo box, drop that there and click. The wizard will start up.
Now the third option down here says find a record on my form based on the value I selected in a combo box. If you don't see this third option, that could be because your form is not based on a table. If this form is based on a query or an SQL statement (basically a query), then you won't get that option. This has to be a table-based form only. That's one restriction.
Hit next. Now what field do you want to search on? Let's go with last name. Just bring last name over. The wizard is going to automatically bring over the ID too. Don't worry about that.
Next. Here's what it's going to look like. See the key column? There's no way around that. That's always going to bring over the ID, but that's okay. We want it there.
Next. What label would you like for it? Don't worry, we're going to delete it anyways, and hit finish. There's the label. Goodbye. Delete that guy.
So here's my combo box to search on last name. I usually tend to make these a slightly different color too. Maybe, I don't know, what goes well here? Maybe like a light purple.
Let's save this. Close it. Open it back up again. If I drop it down, there's my list of people. Pick Picard. Boom. And it jumps right to Picard. See that?
Now, again, I only got six records here, but if I had six thousand, it would take you right down to the ones that you need.
There are a couple of drawbacks with using this method. First of all, you can see these results here aren't sorted. They're just in whatever order they happen to be in the table. You can't sort the table with a query because then you don't get this option for that combo box. But with a little bit of manipulation, you can actually sort the values in this combo box.
So let's double click on it to bring up its properties. Now, it's called combo 17. I don't like that. So let's go in here and change that. Let's call this last name filter. It's actually a search, but I'm going somewhere with this.
Now, go to the data tab. Here's the row source property. The row source is where this combo box gets its source of data from its rows. Hit this little dot, dot, dot button next to that and that'll open up the query builder.
Look at that. It's a little query that represents the SQL statement that select statement inside that row source property. All we want to do in here is sort this list by last name. So watch. It's as simple as sorting that column. That's all.
Now, close this. Save changes. Yes. What that does is it updates the select statement in here. It doesn't actually create a query. We're just modifying the row source.
If I shift that to zoom in, you'll see it. It's a select customer ID and last name from the customer table. It adds this order by last name.
Learn SQL, folks. A lot of good stuff in SQL. Here's another free video for you to go check out: SQL with Access. It gives you all the basics of how SQL statements work in Access.
So now we can save this again, close it, open it up, and now you'll see that this list at least is sorted. If the list is really long, if you start typing in like R-I, it'll bring you right down to Riker. That's how combo boxes work.
Now, unfortunately, there is one more drawback here. That's if you have multiples with the same value. Like, if I got a couple of Rosts in here, like, if I got a Peter Rost and a Joe Rost.
Now, this list doesn't update until you close it and then reopen the form. But now, look, if I drop this down, there's multiple Rosts in there, because each one represents a different customer. That's another downside to using this technique.
But there is a way around that, and we can make this a lot more powerful with just a couple of lines of VB code. Do you want to see how? Do you want to see the trick?
Remember, VB isn't scary. I love showing people just a little bit of VB to get you in to the programming aspect of Access. Once you learn just a couple of little lines of code, your database gets a whole lot more powerful.
Are you ready for some bonus VB action? Here we go.
If you've never done any VB programming whatsoever, pause this video and go watch this. It's free. It's on my YouTube channel. It's on my website. It's 20 minutes long. Teaches you all the basics of what VB is, how to get started, where to find it, that kind of stuff. Go watch this. Don't be scared.
A lot of people think, I don't want to be a programmer. You only have to learn a couple of lines of code to make really cool stuff.
Let's delete the one we've got here. We're not going to use the combo box wizard for this. We're going to do this from scratch using our own combo box. This time, let's do a different field. Let's do state instead.
Let me put some data in our fields. Let's say this guy is also from Florida. This guy is from Texas and this guy is from Texas also. So we got a bunch of different ones with the same state.
First step is to create an aggregate query to group those states together. Create a query where we can group stuff together that's the same, so put all the states that are New York, for example, together.
Go watch this video if you don't know what an aggregate query is.
So, create query design. I'm going to bring in my customer table right there. Close that. Don't need the property sheet. Close that too.
Now, find the field you want to aggregate. Just that, the state. Bring it down here. Hit the totals button. Right there. Now we're going to aggregate query. We're going to group each like state together now and then we're going to sort it also ascending.
If I run this now, there we go. There's a unique list of states in my customer table. Now yeah, you could make a separate table and just put a list of states in there. But the benefit here is that you're only seeing states that are actually in your customer table. If you don't have any customers from Pennsylvania, you won't see it on this list. No reason to search for it if you don't have any.
Save this as my state q, as my state query.
Now I can use this to create my combo box.
Go back into the customer list, design view. Now we're going to create a combo box. Drop it up here. This time we're not going to use the find a record. We're going to do this stuff ourselves. We're actually going to do something better than finding a record. We're going to filter the results in here.
Two lines of code. I'm going to show you how in just a second. But first, I want the combo box to get the values from a table or query. This is called a relational combo box. A relational combo box gets its list of values from another table or query.
Next. Where are we getting our values from? Go to queries. State q. Next. There's only one field in there. That's the state. Next. You can sort it here if you want to, but you don't have to because the query is already sorted. Next. There it is. That's what our results are going to look like. Shrink that up a little bit. Next. Now, what are we going to do? Remember the value for later use or store it in a field. Well, we're not actually storing that anywhere. We're not picking a state and storing it in a record. So we're just going to hang on to it, remember it for later use. Next. What label would you like? Doesn't matter. We're going to delete it. Hit finish. There's our combo box. Get rid of the label. Slide it right above the state, like so. Maybe give it a little color change like we did before.
Now, save it. Close it. Open it. Now, drop this down. You'll see there's a list of my states. But the box by itself doesn't do anything. It just gives you the ability to pick a state from the list of available states. So how do I make it so that this box does something? Well, that's where our VB code comes into effect.
We're going to use something called an after update event. After this box is updated, go do something. Right, after update event. Want to learn more? There's another free video for you to go watch.
So, right click, design view. We're going to open up this guy's properties. Again, combo 19, not very nice. Let's go in here and change that first. We're going to call this the state filter. I'm going to use filter because we're going to be filtering in a second.
The event tab. Here's the after update event. This happens after this value is updated. Dot dot dot, hit that. That'll bring up your VB window. If you get asked what builder you want, pick the code builder. That's all explained in my intro to VB class.
So, state filter after update. What are we going to do in here? I want to set the filter on this form to say the state is equal to whatever I picked in the state combo. If you don't know what a filter is, go watch my filter video. Here's how we set the filter with code. Again, we need two lines of code.
You ready? It's going to be
me.filter = "state = "" " & state filter & " """
What is me? Me is the form you're working on. Right now it's my customer list form. That's me. It has a filter property that we're going to set equal to. Now, this is going to be a little weird. Just follow along with me.
State equals quote quote quote and state combo and quote quote quote quote.
What's with all those double, double quotes? We have to actually write this so it looks like this: state = "New York". But since that's got to go inside a string, the double quotes inside the string have to be changed to double, double quotes like that. So that actually turns into a double quote and that actually turns into a double quote. All of that inside of here ends up looking like this.
Yeah, I know. It's a little tricky. It gets a lot of beginner users. It got me for a while too when I first started learning this stuff. I have a whole separate video that talks about the double, double quotes issue. Watch that if you want to learn more.
So, this sets the filter equal to state equals quote Florida, for example. Now that we've set the property, we have to turn the filter on. So now we say
me.filteron = true
That's it. There's your two lines of code.
Now save it. Close it. Close it. Open it.
Are you ready? I want to see everybody from Florida. Boom. And I get a variable not defined. State combo. What is that? Well, it looks like I called it state filter instead of state combo. Now I'm going to leave this mistake in the video because I goof. I do this all the time. I want you to see the same mistakes that I make. This simply means, I remember now, I called it state filter instead of state combo. Usually I call my combo box something combo. So I'm going to hit the stop button right there. That's going to reset the code.
So, just to double check, design view. Yep. It's state filter. See? Go back to your code. Change this to state filter.
There we go. Now save it. Let's run it again. Close it. Close it. Open it up. Let's see everybody from Florida this time. Ready? Go. There we go. There's our Florida people. Change it again. New York. Beautiful. Change it again. Texas. See? Nice.
One more step. I'm going to give you one more bit of bonus advice here. Wait. What's that? It's a double bonus feature. One more little tip.
What happens if I want to clear this and see all the records again? Delete and then tab, and then I get nothing now. What if I turn this thing off? There they are. We can use this box down here to unfilter it. But what if I want to just make it so that I can just delete this and have it unfilter the list? How do we do that?
Let's go back to our code. Right here, I'm going to say
If IsNull(state filter) Then
me.filteron = false
Else
[this stuff]
End If
There's two new things for you: the If Then Else, and then there's an IsNull. We've got videos for those too. Look for the links down below the video if you want to learn more about IsNull and If Then.
If we come back here now, close this, open it back up again. If I pick something like New York and then I delete that and press tab, it goes away. See, it turns the filter off.
So is that pretty cool? Using that to filter the form. I told you, two lines of VB code, just two lines and you can do all kinds of cool stuff.
Want some more? How about this? How about if I do state and another one like Kirk? Now I don't have anybody that's Kirk and New York. What about Kirk or New York? Or Florida? See? I'll show you how to do multiple filters on the same form and an AND/OR condition. So you can change it from AND to OR, so you're going to get anybody with the last name of Kirk or anybody from Florida instead of the default AND condition. We'll also make a button down here to clear the filters, click one button to clear everything. That, my friends, will be covered in the extended cut for the members.
Remember, as an Up (I think this means as an upgraded member), you get access to all of my extended cut videos. There's tons of them, folks. There's 200 and some of them now, approaching 300, I think. Gold members can download these databases and get access to my code vault and lots of other cool stuff. Plus you get free classes every month. All kinds of neat stuff to become a member.
If you think that learning Access is worth the cost of a cup of coffee once a month, then join. What are you waiting for?
If you want to learn a lot more about combo boxes, my Access Beginner Level 8 class covers lots of different stuff about combo boxes: value list combo boxes, table-based combo boxes, multi-column combo boxes, more with the combo box, search records, and then a whole bunch of other stuff. Again, my Access Beginner Level 8 class. Check it out. You'll find a link to this down below as well.
Do you like this stuff? Are my videos entertaining? Are you learning a few things? Come on, join us on the website. There's cool discussions and forums and all kinds of neat stuff.
Hope you learned something today, folks.
How do you become a member? Click on the join button below the video. After you click the join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos plus my code vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you finish the beginner series.
Platinum members get all the previous perks plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full-length courses found on my website, not just for Access too. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you're a sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website.
But don't worry. These free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free.Quiz Q1. What is the main purpose of creating a search combo box in Access as demonstrated in the video?
A. To allow users to navigate to a specific record by selecting a value from a list
B. To export filtered records to Excel automatically
C. To validate data before saving a new record
D. To back up the database nightly
Q2. Why might you not see the "find a record on my form based on the value I selected in a combo box" option in the combo box wizard?
A. The form is based on a query or SQL statement, not a table
B. The database is not in runtime mode
C. The combo box is in the detail section, not the header
D. Macros are disabled in Access
Q3. What restriction exists when using the combo box wizard's third option to find a record?
A. The form must be table-based, not query-based
B. There cannot be more than 100 records
C. The combo box cannot be placed in the header
D. Only one combo box per form is allowed
Q4. What is a drawback of using the combo box wizard's default method to find records by a field like last name?
A. The list is not sorted by that field unless manually adjusted
B. It always hides duplicate values
C. You must write complex VB code to use it
D. You cannot use it on single forms
Q5. How can you sort the list of values in your search combo box?
A. By editing the row source SQL of the combo box and adding an ORDER BY clause
B. By renaming the combo box to match the field name
C. By adding a sort button to the form
D. By refreshing the table each time it is opened
Q6. What benefit is there in creating an aggregate query to group similar field values (like states) before populating a combo box?
A. It provides a unique list of only those field values present in the table
B. It sorts the entire form automatically
C. It enables the combo box to store multiple values at once
D. It increases the performance of the entire database
Q7. What is a relational combo box in the context of this video?
A. A combo box whose values come from another table or query
B. A combo box that stores multiple values in a single field
C. A combo box with conditional formatting based on related records
D. A combo box that only works on subforms
Q8. How do you make the combo box filter records on the form after a user selects a value?
A. By adding VB code in the combo box's After Update event to set Me.Filter and Me.FilterOn properties
B. By running a macro after the combo box is updated
C. By requerying the form every time the form loads
D. By adding a validation rule to the form
Q9. What code is needed to filter the form by a field (like State) after a selection is made?
A. Set Me.Filter to "state = 'value'" and Me.FilterOn to True
B. Add a new table called FilteredState
C. Create a macro called FilterState
D. Set the combo box value to Null
Q10. What adjustment in the VB code makes it possible to clear the filter and display all records when the combo box is emptied?
A. Checking if the combo box value is Null and setting Me.FilterOn to False
B. Adding a requery command on the combo box
C. Changing the combo box source to a table
D. Deleting the filter property
Q11. If you make a mistake with the control name in your VB code, what will happen?
A. You will get a variable not defined error
B. The combo box will display an empty list
C. The database will crash permanently
D. The records will be deleted
Q12. What technique allows you to display only the search options that actually exist in your data (such as states currently assigned to customers)?
A. Use an aggregate query to provide the combo box's row source
B. Use a static list in the combo box
C. Import state names from Excel
D. Add a filter button on the form
Q13. What does the Me.FilterOn = True statement do?
A. Turns on the filter so the form displays only records matching the criteria
B. Saves the filter to the database as default
C. Updates the underlying table
D. Exports filtered data to a report
Q14. Why would someone use an aggregate query rather than a lookup table for a combo box's source?
A. To ensure only relevant values currently present in records are shown
B. To allow input of new values directly in the combo box
C. Because aggregate queries are faster than tables
D. To display all possible values regardless of records
Q15. According to the video, why should you not fear learning a little bit of VB code in Access?
A. With just a few lines, you can add significant power and features to your database
B. VB always replaces the need for macros
C. Programming is mandatory for all Access work
D. VB code is required even for basic data entry
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 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 video from Access Learning Zone focuses on how to create a search combo box in Microsoft Access that lets you quickly find and filter records in a form using values directly from your data. The idea is to simplify the search process so that, instead of typing in a value, you can just select an existing entry from a list and have Access either jump to or filter for that record.
The inspiration for this lesson comes from a student who enjoyed my earlier search form video but wanted a way to pick from existing values in a field, such as a list of last names, instead of typing them. This approach makes searching faster and less prone to typos.
If you have not watched my Search Form 2.0 video, I recommend starting there to see the base method. That version uses text boxes across the top of a continuous form to enter criteria for fields like city or date, which then filters the results. The method we are covering here is similar but allows for selection from a predefined list, using a combo box.
You can apply this technique to both single and continuous forms, but I find it most helpful on a continuous form, especially if you have a lot of records. For example, with thousands of customers, it is much faster to pick a name from a drop-down list than to scroll manually.
To get started, open your form in design view and make some space in the form header by dragging down the existing controls. Place a combo box above the field you want to search by, such as last name. Use the combo box wizard and pick the option to find a record on your form based on the value selected in the combo box. This option is only available if your form is based directly on a table, not a query. If you do not see this option, double-check the form's record source.
When prompted by the wizard, choose the appropriate field to search, like last name. The wizard may also bring over the ID field by default - that is normal and necessary. The display will show both the ID (which can be hidden later) and the name.
Finish the wizard, then optionally change the color of the combo box for some visual clarity. Now, when you use the combo box, selecting a name will make your form jump right to that record.
There are some limitations to this basic method. First, the combo box values are not sorted by default, as they follow the underlying table's order. To sort the entries, go into the combo box properties, find the row source property, and use the query builder to set the sort order. Sorting by the field you are searching on (such as last name) makes the list easier to navigate.
Another limitation is when there are duplicate values. For example, if you have two customers with the same last name, both will appear in the combo box, each pointing to its own record, which may cause confusion. Also, the list of values in the combo box does not immediately update until the form is closed and reopened.
If you want to make the combo box even more powerful and handle cases such as grouping values or creating dynamic filters, you can use a bit of VBA (Visual Basic for Applications) code.
Let us take the example of searching by state. First, you would create a query that selects only unique values of the state field from your customer table - an aggregate query that groups entries by state. This way, only states present in your dataset appear in the combo box, keeping your filter relevant to the actual data.
After saving the query, you return to your form in design view and add a new combo box. This time, set it to source its values from the query you just made. Place it above the state field, giving it a recognizable name, like state filter, to keep things organized.
To make the combo box filter the form data based on your selection, use the After Update event in the combo box's properties. Insert two lines of VBA code: one that sets the form's filter property to match the selected state and one that turns filtering on. The tricky part is correctly formatting the filter string, especially when dealing with text values and quotation marks.
If you make a mistake, such as referencing the wrong control name in your code, Access will generate an error. Double-check the names in both the code and control properties to ensure they match. Once corrected, saving and reopening the form should allow the combo box to filter your records instantly based on the chosen state.
To reset the filter and show all records, you can add more logic with an If statement. If the combo box is cleared (its value is Null), set the filter off; otherwise, apply the filter. This makes it easy to remove the filter simply by deleting the selection from the combo box.
Once you are comfortable with these basics, you can expand further. For example, you can add additional combo boxes to filter on multiple criteria, use AND or OR conditions between filters, and even create a button to clear all filters in one click. These more advanced features are covered in detail in the extended cut available to members.
If you are interested in exploring more about combo boxes and their various types, such as value list, table-based, and multi-column combo boxes, I recommend my Access Beginner Level 8 course, where these techniques are covered in depth.
Membership on my website offers access to extended cut videos, sample databases, special functions in my code vault, and one free beginner or expert class each month, depending on your level. There are more perks as you move up the membership levels, including access to classes on Word, Excel, and Visual Basic. But even if you stick to my free TechHelp videos, I appreciate your support and participation.
If you want step-by-step video instructions for everything discussed here, complete with on-screen demonstrations, you can find the full video on my website at the link below.
Live long and prosper, my friends.Topic List Creating a search combo box to find records by value
Using the combo box wizard to locate a record
Adding a combo box to a form header
Configuring the combo box to search by last name
Sorting combo box values using the row source property
Editing the row source SQL to include ORDER BY
Renaming combo box controls for clarity
Creating an aggregate query for unique values
Building a combo box based on a query for unique states
Setting up a relational combo box
Writing VBA code for combo box AfterUpdate event
Using Me.Filter and Me.FilterOn in VBA
Handling null values to remove form filters
Implementing code to reset or clear filters via the combo box |
