Drop Down
By Richard Rost
7 months ago
Create Drop-Down Lists in Access + VBA Trick In this Microsoft Access tutorial, we will explore the concept of creating drop-down lists, known as combo boxes in Access, for streamlining data entry. You'll learn how to create value list and relational combo boxes. I'll guide you through setting up basic combo boxes and discuss the pitfalls of using the lookup wizard. Additionally, a developer-level tip on automating combo box drop-downs using VBA is included. This video is tailored for beginners, with insights for intermediate users and developers to enhance their skills. MembersThere is no extended cut, but here is the file download: 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, drop down, dropdown, drop-down, combo box, combo box drop-down, beginner combo box, combo box tutorial, value list combo box, relational combo box, combo box vs list box, lookup wizard, avoid lookup wizard, combo box VBA, VBA drop down, on got focus combo box, value list edits, combo box default value, combo box properties, combo box event, AutoComplete combo box, combo box state, state combo box, set focus combo box, combo box tab order
Subscribe to Drop Down
Get notifications when this page is updated
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost. Today we're gonna talk about drop-down lists in Microsoft Access. Now the words drop-down don't appear anywhere in Microsoft Access, but everyone calls them drop-down lists because it's used in a lot of other applications and like on the web on a website you might see a drop-down list. That's a valid term, but in Microsoft Access we don't call them drop-down lists. In Access, they're called combo boxes, but no one knows what combo boxes are so I'm making this video to talk about drop-down lists. Because if you found this video you were probably searching for a drop-down list.
Now this is gonna be a beginner level video where I'm gonna discuss drop-down lists and how to make some basic ones. I'm gonna refer you to some other videos because I've covered combo boxes in detail in a couple of other videos, so I'm gonna also give you references to those. But we're gonna go over them quickly in this video and stick around to the end. I've got a cool developer level trick when it comes to dropping down a combo box.
First up, why do they call it a combo box in Microsoft Access? Well, it's a combination, a combo, of a text box and a list box. So a text box is like this, where you just type text in it, and a list box looks like this. We've got a list of options you can pick from, which can't type values into it and the list is always open so it takes up a lot of space. So a combo box marries those two things together. You can have a list that you can drop the box down, pick from a list, or you can type in a value too if the designer allows it because you can have it so you have to pick from the list or you can type in your own values just ways to do it both ways. Access does not have just a basic drop-down. Everything you want to do like this is gonna be called a combo box. So now you know the terminology.
All right, so how do you make a combo box? Well, let me start off with my TechHelp free template. This is a free database you can grab off my website if you want a copy, but you can use any database you want. Now one method that you'll see in a lot of tutorials that I completely disagree with is using something called the lookup wizard directly in a table. For example, let's say in the customer table design view, we want to make state a list of options the user can pick from. Well instead of making it short text with a type in a value, drop this box down here and pick lookup wizard. This will allow you to either get the values from a table or query or type in the values that you want. It doesn't matter either way.
All right, let's pick that one, put a list of options in here: New York, Pennsylvania, Florida, and so on. Let's say you only do business in those three states. Next, what label would you like? That's fine. You can pick limit to list here. You want to allow multiple values? That's a whole other problem. You can have multiple select list boxes and then finish. Now, what that does is it creates a lookup list inside this table and if you save the table and go back to table view, come over to state and you'll see now that you could drop this box down, and that seems very handy, right? It's not, it causes lots of problems later on.
Trust me if you have any plans whatsoever of getting semi-serious with your Access database development, do not use the lookup wizard in a table. Don't. It breaks all the rules of a proper relational database. Stay away from it. In fact, when I do my research for my videos, I often go and look and see what other people have done tutorials and videos and websites on, and the top three hits that I got for drop-down lists show how to use the lookup wizard. Don't use the lookup wizard. In fact, I have a whole other video that I'll put a link to down below on why you should avoid the lookup wizard. I'm not gonna talk about it much more today, but just go watch this video if you want to learn more.
Okay, I'm going back to a fresh copy of my database where the state field is just a regular text box, a short text field. There are two kinds of actual combo boxes that you can create. If you want to replace let's say state here, you can create a value list combo box or a relational combo box.
Now a value list combo box lets you type in a list of values here and you can pick from them or you can also allow the user to type in whatever values they want. For example, how do we do that? Just for the purposes of class, I'm gonna make some room here. I'm gonna get rid of the zip and country and let's delete state and we're gonna add a combo box there. Let's make a value list combo box. So find combo box up here right there. Drop it right there. All right, we're gonna select I want to type in the values that I want. Next, put in your list of items, so we got New York, we got Pennsylvania, we got Florida, we got Texas. Again, let's say you only do business in those four states, but occasionally maybe randomly once in a while you get someone else from a different state. All right, next, are we gonna remember the value for later use or store it in a field? I want to store that value in a field, right? Let's store that in my state field. All right, because we're picking text for storing it as text all that works fine. Next, what label would you like? I'm gonna delete it because I've already got one that says city state zip there so I just put in state, that's fine. All right, we can delete that label that it makes and I can probably change this one to just a city state, right like that. Okay, so now I'll slide my state combo box up right there and save it. Close it. I like to always close my forms when I do design work change and reopen them just in case. Open her up. There we go, drop it down and there's your list of options. See New York, Pennsylvania, I can type in as something else if I want to Arizona and it'll take it. You might also notice this little box right there. This is the edit list items button and you can come in here and change that list if you want to. Okay, set a default value. Maybe you are in Florida and most of your customers are in Florida. Hit okay, and now if you go to a new record you'll see Florida is the default down. That's pretty cool.
Of course there are some options in the property that you should know about. Open up the properties. Double click on the box here if you don't see the property sheet. Now first of all it's called combo with 30. I don't like that so I'm gonna go to the all tab and let's give this guy a good name. Its control source is state. I mean, that's where it's storing its value. It's bound to the field in the table called control source but we're gonna also give it a name. I'm gonna call it state combo. You can just call it state if you want to but that just tells me it's a combo box. Now on the data tab, right here is your limit to list option if you set that to yes, then the users have to pick an item from your list. They can't just type in whatever they want. Even if you do leave that as set to no, you can allow or disallow value list edits, so even though they can type in their own stuff they can't go in and change that list. If they can add Hawaii to their customer if they want to but they can't add Hawaii to the list.
Now there's a lot more you can do with value list combo boxes. If you want to learn more go watch this video. I'll put a link down below. Now value list combo boxes are cool and they're fine for just one off little things. But what if you want to use that same list of states in different forms or different reports, right? It's stuck here in this form. That combo box is the only place that list exists. Right, so what about things like a customer list or a list of, you know, sales reps? For that you're going to want to use something called a relational combo box down. They're a little more complicated.
Now if you don't know about relationships between tables you should go watch my relationships videos like how to link customers to vehicles that kind of stuff. To make this kind of relationship we're going to need to store a state id in the customer instead of the actual text of the state and we're going to need a table to store that list of states. Okay, so let's close this. They've changed it now we're going to come back to it. Let's make a table to store our list of states right create and then table design and I'm going to have a state id that's my auto number.
The state maybe the state abbreviation and I usually put a brief like that because abbreviation for talking about something that's so short abbreviation is really long, so I just always do a state of brief like that. That's going to be short text and then maybe the full state name if you want. Save it. We'll call this the state t the state table. There's no primary key to find access always pops this up if you say yes. It's going to make that the primary key which is fine as the auto number and then we'll put some values in. All right, we got NY New York. We got FL Florida. We got TX for Texas when I say T. I don't know AK for Alaska. Whatever else you want to put in here you can put all 50 of them in there if you want to. Save it.
Now what we're going to do is we're going to save that state id wherever we want to use this list of states like in our customer table. So we're going to get rid of that go into our customer table design view. We're going to get rid of this state now. If you want to keep all your existing states, there's tricks you can play. You can use some update queries to copy the current state over into a state id. I got a whole separate video on that. Here's a video on update queries. You'll just make an update query to convert from the text over to the id. But what I'm going to do here is let's delete this state just for the purposes of class. I'm going to add a state id on the bottom here state id and that'll be a number. We're going to store the id from the state table in that field. So come back to the customer form, design view. Notice this is no longer valid. That's what that little green thing there says right. It says no such field. That's fine. Goodbye.
Now we'll make a properly relational combo box same thing combo box wizard drop it there. This time I want the combo box to get the values from another table or query. Next, which table or query should provide the values while I'm getting my list of values from the state table. Next, what fields do you want? Well, you can bring over all three of them if you want to, you're only going to see this guy when the box is closed, but you'll see both of these once when it's open. That's helpful and the id will always be hidden if you hit next you'll see right here we got a sort let's sort based on the state abbreviation. Next and here we go hide key column that hides that id. You don't really want to see that do you? No hide it and then you can resize these columns like this so it looks nice and pretty right next. Now you want to remember that value for later use don't pick this one. We're almost never going to pick this one. This should be the default option. We're going to store that value in what field we're going to pick a state and we're going to store it in the state id in the customer table. All right, this is a list of fields from the customer table store it in that state id. Hit next. What label would you like to give for the combo box? Well, again, we're going to delete the label. It doesn't ask you for a name which it should and then hit finish. All right, there's that label get rid of the label. Slide that combo box up on here. Make it even like that. Let's give it a good name. Let's roll up to the top here. Let's call this again the state combo. Save it, close it, and then open it back up again and there we go there's our New York floor to Alaska list right. Now when I picked Florida for me if I close this and go back to the customer table, notice that it's stored a two in there because Florida is state id two and that's fine. That's how you build a properly relational database and the benefit is I can use this state table in other forms now. I can use it in my order form. I can use it in my contacts form. I can use it wherever I want to and it's the same list if I want to change or add things to this list I just do it here. I don't have to do it in five different places. If you want to learn more about relational combo boxes go watch this video. I'll put a link down below. There's also a third kind of combo box that that wizard can make. It's called a find direct or to combo box and you can use it to find the values in your form with a little search box. So go watch this video if you want to learn more about that. That little box that pops up or let you add and edit items in the box. It's called a list items edit form you can make your own custom form to be able to edit those items instead of the default one that pops up. This is pretty cool to go watch this video.
As promised at the beginning of the video I got a little tip for the developers here hang on a second. There we go. We'll bring developer to the front. All right. Here's a little developer trick a little VBA. What is VBA? Stanza visual basic for application. Just the programming language behind Microsoft Access and Excel and Word and PowerPoint. It's very very powerful that you do lots of stuff. You can learn all the basics in about 20 minutes go watch this video if you want to learn more. Now there's a VBA method called drop down which you can use when let's say for example you're tabbing and you get to this field you want the box to open automatically for you. You call the drop down method.
Now the first thing I do is make sure that this box is in the tab order in the right place. Because it's going to be the last control I added this to this form. So it'll be last in the tab order. So let's fix that. So here is state combo. See it's last. Let's put it after city so if I'm on city and I hit tab it goes to the state combo box. That's another reason why you want to give it a good name. So it's not combo 30 in this list. Now go to the box find its event tab. You want to find the on got focus event. This event runs when the box gets focused which means that you either tab to it or you clicked on it. Hit the dot dot dot button that'll bring up your Visual Basic editor. I got pretty big. Let me resize it there it is. Turn off the immediate window.In the state combo got focus subroutine, we're going to type in here state combo dot drop down. That's it. So I need one line of code. A lot of the times the coolest solutions are just things you need one line of code for. Close that and now watch this: close the form, open it back up again, and now if I'm tabbing, tab, tab, tab, tab, tab, look at that, it opened up the box for me. See, that's the drop down method. I'm the kind of person who, when doing a lot of data entry, doesn't like to have to stop and grab the mouse. So I'm able to tab through it and then use the arrow keys and then tab away.
You can also do the same thing from somewhere else. Let's say you want to make a button over here that'll go to and drop down this box. So we're going to go in here. I'm just going to copy and paste that button. Whatever you want is the caption. All right, right-click, build event. Now, before you can drop it down, you have to move to it, so you have to say state combo dot set focus. That'll move the focus onto that box. Now, because there's already a got focus event in here, you can just rely on this one to drop it down. But if not, if you don't have the event actually in here, you can just put it up here. Okay, but you don't need both of them. It's already down here in the got focus; you can just do that.
All right, save it. Debug compile once in a while to make sure everything's okay. Close it, open it, hit the button, boom, there you go. Pretty cool.
All right, if you like learning this kind of stuff, I have tons of lessons on my website for Access users of all levels. If you're a beginner, I've got lots of beginner levels. If you're an expert, I've got lots of lessons for you. If you're a developer, I have 50 levels now of developer training, so tons and tons of stuff to learn. I have extra seminars and templates, and you name it. I've got all kinds of Microsoft Access courses on my website, check it out. Level one is free if you haven't watched it, you'll find links down below.
So that's gonna do it. That's what a drop down list is in Microsoft Access. It doesn't exist; it's called a combo box. No, I know everyone always asks me about drop down lists, but that's the right terminology. It's combo box, and now you know. And knowing is half the battle.
That's gonna be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Creating a combo box in Access
Difference between list box and combo box
Avoiding the lookup wizard in Access
Value list combo boxes
Setting properties for combo boxes
Relational combo boxes
Creating a table for state list
Storing state ID in the customer table
Using combo boxes across forms
Dropdown method in VBA for combo boxes
VBA code to automatically drop down combo box
Setting tab order for form controls
Adding a button to trigger combo box dropdown
COMMERCIAL:
In today's video, we're talking about drop-down lists in Microsoft Access, known as combo boxes. You'll learn how to create basic combo boxes, including value list and relational combo boxes for linking data across tables. We'll explore the pitfalls of using the lookup wizard and why it's best avoided. You'll see how to make a combo box that fetches data from a different table, ensuring consistency across forms. Plus, for you developers, we have a cool trick using VBA to make your combo box drop down automatically as you tab through fields. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper, my friends.
Quiz
Q1. In Microsoft Access, what is the correct term for what is commonly referred to as a "drop-down list"?
A. List box
B. Combo list
C. Drop box
D. Combo box
Q2. Why is a combo box called a "combo" in Microsoft Access?
A. It is a combination of two tables.
B. It is a combination of a query and a form.
C. It is a combination of a text box and a list box.
D. It is a combination of multiple drop-downs.
Q3. What is one reason why you should avoid using the lookup wizard directly in a table?
A. It does not allow for any customization.
B. It can only be used on existing records.
C. It breaks the rules of a proper relational database.
D. It only works on primary key fields.
Q4. What is a key feature of a value list combo box?
A. It automatically updates data in all tables.
B. It allows users to type only predefined values.
C. It enables users to type in or select from a set list of values.
D. It requires a separate form for data entry.
Q5. What is the main advantage of using a relational combo box over a value list combo box?
A. It requires less setup and coding.
B. It allows for real-time data linking between forms and tables.
C. It is easier to implement for beginners.
D. It can be used across multiple forms and reports by linking to a table.
Q6. What does the "limit to list" property in a combo box do?
A. It allows the user to only see a limited number of records.
B. It restricts users to select only from the listed items.
C. It limits the number of combo boxes on a form.
D. It limits the combo box to ten items only.
Q7. What does the VBA "drop down" method do in a combo box?
A. It creates a drop-down menu from a report.
B. It opens the drop-down part of the combo box automatically.
C. It duplicates the combo box values into another field.
D. It disables the combo box from opening manually.
Q8. Why should you give your combo box a meaningful name, such as "stateCombo"?
A. It ensures better compatibility with other software.
B. It improves the form's design aesthetics.
C. It helps in identifying and managing controls in forms and code.
D. It increases the default response time of the form.
Answers: 1-D; 2-C; 3-C; 4-C; 5-D; 6-B; 7-B; 8-C.
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 focuses on creating and using combo boxes in Microsoft Access. Although commonly referred to as 'drop-down lists' due to their prevalence in other software and web applications, in Access these elements are known as combo boxes. My aim here is to clarify this terminology and guide you through the basics of creating combo boxes.
This beginner-level lesson will cover how to construct basic combo boxes, which allow users to either select from a predefined list or input their own data, depending on the design settings. While I will summarize the process here, I recommend checking out additional detailed videos I've made on combo boxes for more in-depth information. Stay tuned for a developer-level tip concerning combo boxes at the end.
First, let's address why Microsoft Access refers to these as combo boxes. A combo box combines features of a text box, which accepts user input, and a list box, which displays a set list of options. Unlike a list box, which is always open, a combo box saves space by allowing the list to appear only when needed, while maintaining the capacity for user input if permitted.
To create a combo box, start with the database of your choice. It's important to move away from using the lookup wizard directly in tables—a common practice in many tutorials but one that disrupts the principles of relational database design. This wizard might seem convenient for creating a list directly within a table's field, such as generating a list of states, but it can cause problems as your database becomes more advanced.
For a proper approach, you can develop a combo box in a form. There are two primary methods: using a value list combo box or employing a relational combo box. A value list combo box is best when you want a simple list that can either be static or editable by the user.
Conversely, for a more scalable option, a relational combo box utilizes data from a separate table, which allows for reusability across various forms and reports without having to reenter the data list each time. This method ensures that updates to your list of options need only be made in one place.
Once you've set up your combo box, you can refine it with settings such as Limit To List to control user inputs more precisely. Beyond this, combo boxes can be expanded with VBA (Visual Basic for Applications) coding to add functionality. For example, you can automate list display upon selection focus, enabling smoother data entry workflows.
If you're interested in further enhancing your combo box, you might consider creating custom forms for editing list items, leveraging additional VBA magic for tailored control over your database's user interface.
This session includes a special trick for developers, involving a straightforward application of VBA: using the 'DropDown' method to automatically open a combo box during tabbing. This technique streamlines data entry by eliminating the need for mouse navigation. It also illustrates the potency of even simple VBA scripting in customizing user interaction with Access forms.
For those of you eager to dive deeper into Access capabilities, there are a wide array of lessons available on my website, supporting users at every skill level—from beginners to advanced developers. I've covered many aspects of Access development, including specialized seminars and templates. I strongly encourage you to explore these resources.
This concludes today's discussion on drop-down lists—better known as combo boxes—in Microsoft Access. I hope this clarifies the concept and assists with your database development journey.
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 combo box in Access
Difference between list box and combo box
Avoiding the lookup wizard in Access
Value list combo boxes
Setting properties for combo boxes
Relational combo boxes
Creating a table for state list
Storing state ID in the customer table
Using combo boxes across forms
Dropdown method in VBA for combo boxes
VBA code to automatically drop down combo box
Setting tab order for form controls
Adding a button to trigger combo box dropdown
|
