Multi Combo One Field
By Richard Rost
4 years ago
Multiple Combo Boxes for One Field in Access
In this Microsoft Access tutorial, I'm going to show you how to bind three different combo boxes to your order form to pick the customer. This will allow you to easily find the customer based on his name, company, or phone number.
Recommended Course
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, #fasttips, multiple combo boxes, same field, search for customer
Intro In this video, we'll talk about how to use multiple combo boxes bound to the same field in Microsoft Access forms. You'll see how this technique allows users to select a customer in different ways, such as by last name, company name, or phone number, all while updating the same field in your order form or similar layouts. We'll walk through setting up specific queries for each search method, adding the combo boxes, and explaining how they work together to provide a flexible way to find and select records more efficiently.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com.
I am your instructor, Richard Rost. In today's video, we're going to talk about multiple combo boxes for the same field. Why would you want to do this? What's the benefit? And how do you set it up?
This is a handy trick that I do once in a while, especially if you want to set a value for, let's say, a customer in some form, such as an order form or an appointment form, but how you find that customer might be different based on the situation. For example, let's say you're putting orders in the system and you open up your order form.
Here, I can pick the customer with this customer dropdown, but it's only got last name, first name. However, you might also want to look up the customer by their company name, phone number, or address. Depending on your business, it might be different. If you run a delivery business, you might often look customers up by their address, street, or phone number.
So, what you can do is bind multiple combo boxes to the same field but look up the data differently based on what you put in the combo box.
In my TechHelp database, for those of you who are familiar with it, I added a company name field. We might also want to look up customers by company name, address, or phone number.
How would we set this up?
I've already set up a customer last name, first name query, which I showed how to do in the blank template. This is where you take two fields and concatenate them together. If you haven't watched this video, go watch it now. It's how I build this basic template database. There's a link to it right there. Also, watch the invoicing video as well – it is where I make the order form, and I think I put the combo box together in the invoicing one.
Back to the database – this query is the key. This is my customer last-first. So, I have the customer ID and then the last name, first name. You could also do one for first name, last name if you want to sort that way. But this is basically what I see in that combo box.
Remember, we are storing a customer ID in the order table. That customer ID is what our combo box actually picks. The second column is just what's displayed for the user. You can have multiple displayed columns if you want to.
We are going to set up another couple of queries that have what we want to see in this box, but they're all going to be picking a customer ID and saving the customer ID in that record in the order table.
Let's make another query. Create query design. I'm still going to use the customer table. I'm going to bring in the customer ID and then decide what I want to see in the box. I want to see the company name. If I run that, this is what it will look like.
Now, I want to avoid blanks. There's no sense in having blanks in here. We'll just come down here underneath in the criteria and say Is Not Null. There's a list of my companies.
The combo box will take care of sorting it, so you don't have to sort the query if you don't want to. But you can if you want to use it in other places.
Save this as customer company query.
Let's say also you want to be able to look them up by phone number. We'll do three of them.
Again, create query design. Can you do this without making multiple queries? Yes, of course, you can if you know your SQL. But I'll keep this video simple. If you know SQL, you don't need to make multiple queries. You can just change the SQL that is in the combo box. But we'll keep this so everyone can do it.
So this will be the customer ID and then their phone number. Now, you might also want to have first name and last name in here as well. When you run this query, you'll see that in the box when it's closed, but when you pick that one, you'll see details when the box is open. So you'll know for a fact that's her.
Call this customer phone query. Get rid of null values down here by setting the criteria to Is Not Null. If you want to sort it, you can sort it. Save that as customer phone. Let's also put in the customer company query; let's also add first name and last name as well.
When you run it, you will see, for example, Beard Co, and realize that's the whole record. Now, we have three different queries and now we can put two more combo boxes on our order form.
Watch what happens. Go in here to design view. It doesn't matter if this is an order form, appointment form, service ticket form, or whatever. You are basically giving the user three different ways to pick the same customer.
I'm going to make this subform a little smaller so we can fit those other combo boxes here.
Here we go with some combo boxes. If you've never made relational combo boxes before, go watch this video. It's free, available on my YouTube channel and my website. This is a prerequisite from the invoicing video, so if you watched that, you've watched this.
Many people ask why they have to jump around to so many different videos. These are TechHelp videos and Fast Tips. This is not my full course. My course is designed in order, so you watch step one, step two, step three, step four, and you don't have to bounce around. That's why I package it as a course.
But for these tip videos, you have to know this to know that. I will give you the links to the videos if possible.
Let's go ahead and find a combo box. This will also work with list boxes, by the way.
Drop that there. Let's do the company name one first. Find the values in the table or query. What table or query has the data? Customer company query.
What fields do you want to see in the box? Bring them all over.
Next. What do you want to sort by? Let's sort by company name. If you want to add multiple sorts, you can then sort by first name and then last name.
Next.
This is what the columns in your combo box are going to look like. If this was based on a table, you'd see a little checkbox here labeled Hide Key Column. Since your combo box is based on a query, you don't get that checkbox. You have to hide this manually by making the width zero. Grab it and click-drag to make that zero width.
It's still in there – you need it to save the value – but you don't necessarily want to see it.
This first column is visible when the box is closed, then you have these two that will pop up when the box is open.
Next.
Which field is the bound field? Which field do you want to save? Customer ID. We are picking an ID.
Next. We are going to store that value in a customer ID.
Next. What label would you like for it? We'll put Company in the label.
There we go. I'm going to use the format painter here. Click Format Painter. Just make this label black. Slide that there. Slide this this way.
Now we have two combo boxes that both say Customer ID in them. Let's make sure they each have good unique names and not just Combo14. This one's CustomerCombo, this one's going to be CompanyCombo.
If you look under Data, they're both bound to the control source – that's CustomerID.
What does this look like? Watch this.
Open it up. Let me open it from out here, since the customer form will only open up that customer. Open up the blank one and go to a new record.
If I pick a customer from here, it automatically fills that one in. If I want to change this, and look for someone else by their company name, I change it to that, and look, it changes the other one too. Both of those combo boxes are bound to the same field. If you change one, you change them all.
Let's do another one.
Drop this down. Combo box. Put it there.
Next, queries, customer phone query.
Next. Bring over all the fields. We are going to sort by phone number. You shouldn't have two phone numbers that are identical unless you don't have an index. Sometimes that happens.
Drag that down. Close that.
Next. CustomerID is the bound field. Store it again in CustomerID.
Next. Label it Phone.
A little format painter action. Slide you up there. Slide you right there.
Now we've got three fields that are all based on the same thing.
Save it, close it. Now when I go to create an order, go to a new blank order. The other customer comes in, and if it's a walk-in and you have 5,000 customers in here, the easiest thing might be, "What's your phone number, sir?" Enter 5555, and it goes right to that one.
Then, you can drop the box down if you want to and see, "Oh, are you James Kirk?" OK. There you go.
You could do one with address, you could do one with email – whatever you want to search by. This saves you a step from making a big search form where you have to try to find the customer first.
Instead, you can just put multiple combo boxes on the order, so you can change this here and it changes everything.
That is the difference between controls and fields. Fields are in your table back here – these are fields. A field comes into your form into a control. The control is bound to a field, and you can have multiple controls, like combo boxes, that are all bound to the same field to make your job easier.
That works. It's a cool little trick. I have done this a couple of times in the past in some databases I built for clients. I do not think I have covered this in any of my classes.
Sometimes even if you are one of my regular students and you watch all my classes, another reason for these TechHelp and tip videos is to cover weird stuff like this that doesn't necessarily fit into one of my other classes.
So there you go. That's your Fast Tip for today. I hope you learned something. Sign up and become a member and learn lots more cool stuff.
Thanks. We will see you next time. Take care.
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've finished 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. 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 are 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.
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 benefit of having multiple combo boxes for the same field on a form?
A. It allows users to search and select records using different criteria, such as name, company, or phone.
B. It lets users edit multiple fields simultaneously.
C. It prevents duplicate data entry for the same field.
D. It restricts users to one method of data selection.
Q2. In the example given, which field is actually stored in the order table when using the combo box to select a customer?
A. Customer Last Name
B. Company Name
C. Customer Phone Number
D. Customer ID
Q3. How are different combo boxes on the same form able to update each other automatically?
A. They are all bound to the same field in the table.
B. They have identical names in the form.
C. They use separate queries for each combo box.
D. They are controlled by a macro that syncs their values.
Q4. When setting up a combo box to list customers by company name, which fields should typically be included in its query for better usability?
A. Only the company name
B. Company name, first name, and last name
C. Only the customer ID
D. Company name and phone number only
Q5. Why should you set the criteria "Is Not Null" in a combo box query?
A. To display only active customers
B. To avoid listing records with blank values in the look-up field
C. To show only the first record
D. So users can add new entries directly from the combo box
Q6. What should you do if you do not want the user to see the ID column in a combo box?
A. Delete the ID column from the query
B. Set the column width to zero manually
C. Hide the combo box on the form
D. Turn off the bound column property
Q7. Which statements about using queries for combo boxes are TRUE in the context of the video?
A. You must create a separate query for each different way you want to look up data.
B. If you know SQL, you can reuse one query by changing SQL in the combo box's Row Source.
C. Combo boxes can only be based on tables, not queries.
D. You cannot sort combo box data.
Q8. In Access, what is the difference between a field and a control as explained in the video?
A. Fields are displayed while controls contain values for lookup
B. Controls are inside tables, fields are on forms
C. A field is stored in the table, a control is the form object bound to the field
D. There is no difference; they are the same thing
Q9. How can you give each combo box on a form a unique identifier for easier management?
A. By changing the control's name property in the form design view
B. By using a different table for each combo box
C. By assigning a unique index value in the table
D. By creating them in separate forms
Q10. Which of the following is NOT a recommended use case for this multiple combo box technique?
A. Looking up a customer by company name
B. Selecting a customer by phone number
C. Searching for a customer by address
D. Storing multiple values for the same field in one record
Answers: 1-A; 2-D; 3-A; 4-B; 5-B; 6-B; 7-B; 8-C; 9-A; 10-D
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 setting up multiple combo boxes for the same field in Microsoft Access. I often use this technique when I need to let users pick a value, such as a customer, on forms like order or appointment forms. The reason for offering more than one combo box for the same field is that users might want to find the customer using different information depending on the situation. For example, one user might prefer to search by last name, while another might want to look up a customer by company name, phone number, or even address.
Imagine you have an order entry form with a customer dropdown that currently only lists last name and first name. This works for some businesses, but for others such as a delivery company, searching by address or phone number might be more useful. By creating multiple combo boxes, each using a different query to organize or display the data, you make your forms much more flexible. Behind the scenes, all these combo boxes are still bound to the same customer ID field in your order table.
To set this up, you start by designing queries for each way you want to look up the customer. For example, I already have a customer last name, first name query that pulls those two fields together. If you watched my video where I build the basic template database, you'll recognize this query. I always make sure the combo box is storing the customer ID; the other values you see, like name or company, are just for display.
Next, I created a new query to let users search by company name. This query selects the customer ID and company name fields. I also made sure to exclude records where the company name is blank so the combo box is clean and relevant. Sorting is optional, since the combo box itself can handle sorting, though you might want to set the sort order in the query if you plan to use it elsewhere.
After that, I built another query for searching by phone number in a very similar way. While it is possible for users who know SQL well to just edit the SQL for their combo boxes directly, I show how to do it with separate queries to keep things simple and approachable.
Once you have the needed queries, you add the new combo boxes to your order form. It does not matter which kind of form you're working with; the approach is the same. Each new combo box is bound to the same customer ID field but gets its row source from a different query, so users can search by company, phone, or whichever method you decide is best.
When you set up a combo box, you choose the query to use, bring over all the fields you want to display, and pick the sorting order. If your combo box is based on a query, you need to hide the key column (customer ID) manually by setting its width to zero. This keeps the form tidy while still allowing the combo box to work as designed.
Each combo box should have a meaningful name, such as CustomerCombo, CompanyCombo, or PhoneCombo. They all are bound to the customer ID field, which means when a user picks a value in any one of them, the customer selection on the form updates everywhere automatically. If you use more than one, you give your users the flexibility to work the way that suits them best.
You can take this idea further by creating combo boxes that look up customers by other fields too, such as address or email. This removes the need for a separate, complicated search form on your database. Instead, you put multiple combo boxes right on your data entry form to make customer selection more efficient.
This method takes advantage of the distinction between controls and fields in Access. Fields live in your tables and store your data, while controls on your forms are the interface through which you view and edit that data. You can have multiple controls, like combo boxes, all bound to the same field, and changing one will update all the others automatically.
This is a handy trick that I've used in several client databases but have not covered in the main courses. Tip videos like this one are great for exploring useful features and tricks that do not fit neatly in a larger structured class.
If you want to keep learning, consider becoming a member. Members get access to additional content and perks at various levels. Silver members and up can watch all my extended cut TechHelp videos and get a free beginner class each month. Gold members can download sample databases and have higher priority for TechHelp questions, plus receive a free expert class each month. Platinum members get everything mentioned already, plus priority support, all beginner courses, and monthly developer classes in subjects beyond just Access.
Remember, all of my regular TechHelp videos will always remain freely available as long as you keep watching and supporting them.
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 Using multiple combo boxes for the same field
Binding combo boxes to the same field for different lookups
Creating queries to display different customer fields
Avoiding null values in combo box queries
Concatenating fields in a query for combo display
Configuring combo box display columns and bound fields
Hiding key columns in combo boxes
Assigning unique names to form controls
Synchronizing combo boxes through shared control sources
Adding and configuring combo boxes for alternate search methods
Sorting combo box lists by specific fields
Using the Format Painter to customize combo box labels
Explaining the difference between fields and controls in Access
Using combo boxes to streamline record selection in forms |
