Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost. In today's video, we're going to be building a combo box in Microsoft Access that gets its values from another table or query. I will show you how to pick a customer from a list of customers when filling out service forms or orders.

Today's question comes from Marie in Chandler, Arizona, one of my Platinum members. Marie says, "I have a simple service call form where I enter the customer's name, the service date, and a brief description of what needs to be done. Is there any way that I can pick the customer from a list instead of having to enter in their info each time I make a service ticket?"

Well, yes, Marie. In Access, you can use either a combo box or a list box to pick from a list of values. In this video, I'm going to show you how to make a relational combo box.

Before we get started, I want you to go watch my value list combo boxes video. In that video, we make a small list of options to pick from. For example, title, Mr., Mrs., Miss, and so on. The problem with a value list combo box, however, is that if you want to use that combo box on multiple forms, you have to update it everywhere it exists.

So, a relational combo box lets you get a list of options, a list of values from a table or query. For example, you can pick a customer from the customer table and store that information on your order form, on your service form, etc. So, every form that it exists on, you don't have to update every single combo box.

Go watch the value list combo boxes first, so you know how to build a basic combo box. Then I want you to go watch the relationships video. It's very important that you understand the relationships between two tables, using a customer ID for example, before we continue with this video.

I also want you to go watch the concatenation video, that's where you put together two strings. We're going to put together first name and last name so they appear together in the combo box. And if you're new to my TechHelp videos, go watch the blank database template where I explain how I build my blank database template that we're going to use in this video.

These are all free videos. They're on my website. They're on my YouTube channel. I will put links down below in the description below the video. Go click on these. Go watch these videos first. I'll wait for you. Go on. Go do it. Go watch them.

All right. You back? Okay, good.

So here I am in the TechHelp free template. This is a free download off my website. You can go grab a copy. Again, I'll put a link down in the link section below. As I said before, in the value list combo box video, we made a list of titles, but it was set in that combo box. All the data was in that combo box specific to that form. So, let's recreate that combo box, but we'll make it relational.

First, we need a table to store our list of values. So, create, table design. We'll have a title ID, that's our auto number. Most tables should have an auto number. Then maybe the title name. All right, short text. Save it. Title, T, our title table. Primary key, yes, that's our auto number.

All right, and let's put some values in the title name. Let's put in here, Mr. Miss Miss Mrs., maybe Doctor. All right, you can edit this list in the future if you want to. Okay? And all of the combo boxes on your different forms that have this information will automatically update. Okay? Save it. Close it.

Now we're going to store the title ID in the customer table. So, each customer has to have a place to store it. So, I'm going to edit the customer table now, come down to the end, put in here a title ID. This is going to be a number of type long integer. It's a foreign key. We learned about that in the relationships video. So, again, if you haven't watched the relationships video, go watch it. We’re not storing the text. We’re not storing the title name in here. We’re storing the title ID. Now I'm going to click and drag this up top, put it right up out here maybe. Okay. All right. Save it. Close it.

Now we can put that combo box on the customer form that draws its list of items from the title table. So, let's go to the customer form, design view. All right, let's make some room for it. I'm going to slide all of this stuff down just a little bit so I can put a combo box right there. All right. Let's go up top. Let's grab a combo box, drop it right there.

The wizard starts out. I want the combo box to get the values from another table or query. We're not getting it from this table. We're working with the customer table here. But I want another table or query to provide the values. Next, which table or query should provide the values for the combo box? I'm getting my list of values from the title table. What fields do you want in the combo box?

Now first you need the ID, because we're storing the ID in the customer table. But I also want to bring over the title name, because that's what's going to be visible in the box. So, it's going to have number one, Mr. And they'll both be in the box, but this guy will be hidden. You'll see how this works in just a second. Next.

Now you can sort the values in the field. We're going to sort by the title name. So, the box will be sorted by title name. Next. Now, what do the combo box columns look like, the columns in the box. Now there's normally two of them. We brought over two fields, the title ID and the title name. We're going to hide the key column. You don't want to see that. I don't care what that number is. That auto number is just for Access to make the relationship. That's the value we're going to store in the customer tables, but I don't need to see it. You could probably make this shorter, too. Next.

Now, once I pick a value, what do I want to do with that value? Remember it for later use? Or store that value in a field? Well, I want to pick a title ID, and I'm going to store that in the title ID field of the customer table. See how that works? We’re picking a title from the title table, and we’re storing it in the title ID of the customer table. That's how a combo box makes the relationship for you, without you having to know what these IDs are. Next.

What label would you like for your combo box? That's just a little label that goes next to it over there, so I'm going to put in here title, like that. And then I'll hit Finish. And there's my combo box. Now I'm gonna slide it up a little bit like that and I'll use the format painter which is right there and I'll paint over that guy so it's black. And now I've got my combo box.

Now, let's close this save changes. Yes, and open up the customer form. Drop it down and look at that. There's my list of values. Mr. Richard Rost, go to the next record. Mr. James Kirk. Miss Deanna Troy, well she was at first, then she got married to Will. Jean-Luc, Mr., there you go, and I just go through and pick the right ones. Mr. Will Riker. Dr. Malcolm Reynolds.

Now the benefit of this, once again, is if you have another form that has the customer information on it, you can just put this combo box on that form. And this list of value items here will update if you add more to the table. So, if I come in here in the title table, and I put in here, you know, Ms. All right, close that, go back in here, and now you'll see Ms. is now an option. Okay?

So now we know how to make a simple relational combo box with just one item in it. What if you want to pick a customer based on last name, first name, for example? Well, that's why I wanted you to watch the concatenation video. You learned how to do this. Take first name and last name and put them together. So, you got last name, first name, like that. Or we could do another one here. We'll do first name, last name. So, let's go FL is going to be first name and a space and last name. We did that in the concatenation video. We learned how to do that. If I run this now, you can see I got it both ways. I got LF and I've got FL. And that's in my query, my customer LFQ. Okay? Let's save that, close it.

The reason why we do this is because in that combo box you can only have one visible column. So, it'll either be the first name or the last name. So, if you want that combo box, when it's closed, right, to show first name and last name, you have to put them into one column, which means you have to put them into a single field in a query using concatenation.

Okay, so now we got this. Now we can go ahead and build a service form and pick a customer to put on the service form. So, first let's make a service table. Create table design. Service ID, that's our auto number. Service date is a date time. I'm going to default that to equals now. That'll put now, the current date and time in that field.

Don't just call it date, call it service date because date is a reserved word. It's got a special meaning in Access. So does the word name, that's a reserved word. You don't just use name, first name, last name, that kind of stuff. There's a whole big, long list of reserved words. My buddy Alex put it together. It's on my website. I'll put a link down in the link section down below.

Now we need to know which customer this service call is for. So here we're going to put the customer ID and we're going to enter in the customer ID as a number. That is our foreign key that relates to the customer table. We do not type in the customer's first name and last name. That breaks the rules of relational databases. Marie, that's how your database was set up. You were typing in first name and last name. But we're going to store the customer ID now, make this properly relational.

Then we need to know what's the service call for. I like to put in description, which is short text, and then notes, which is long text. Long text used to be called memo. The reason being is because descriptions are like a brief, what's this call about, then the notes can be all the details. The descriptions can show up in things like list boxes and you can sort by them. There's things you can do with short text that you can't do with long text. So I like to make a header, like a brief description that's short text, and then long text can be whatever else you want to put in there.

Save this. This will be my service T. No primary key defined. Yes, there it is. Now I can close this and let's go make a service form.

If you watched my blank database template video, you know I've got this guy right here called single F. It's a blank form already set up the way I like it. So, I'm going to copy and paste it. Copy, paste, service F, that's my service form, no need to reinvent the wheel. Right-click, design view, there it is, that's my basic service form. The first thing to do, double-click right here to bring up the service form's properties, find the record source, and we're going to bind this to our service table. So, this form is now based on the service table.

I'm now going to add existing fields. There's the fields for my service table. Pick the first one, hold the shift key down, click on the last one, that highlights all of them. Click and drag and drop them right there. The only reason I keep these guys around is to use the format painter. So, click, format paint, click. I like to make my IDs gray, that way the users know they can't change them. And then click, double-click on that guy, and then go click, click, click. Turn the Format Painter back off. Now I can delete these. See, it makes it nice, quick, and simple. Slide all this stuff up here. Close that. Shrink that up. Save it. Close it.

Reopen it. That's what it looks like. The service date's a bit too big. Because there's a date and time in there. Right-click, Design View. Let's just drag all these out like this. Plenty of room in there and then maybe left align them. Left align that guy too. I like everything lined up to the left. Let's take one more look at it. There we go, looks good.

Now, customer ID is a number field. I don't want to have to type a number in. I don't want to have to know who customer ID 3 is. So that's where I'm going to delete this guy and let's add a combo box right there. Drop them. There we go. Look up the values from another table or query. Next, which table or query should provide the values for your combo box?

Now, don't pick customer T because we want to get our list of values from that query that we made, right, that's got the last name and first name together in one field. So, I'm going to go to Queries and pick Customer LFQ. A little bit of trivia, this is one of the reasons why I started using Ts and Qs on the ends of my tables and queries. Because in the older versions of Access, I don't know, I started using Access in like 1994. You just got one list of all the tables and queries. And it didn't say table or query or break them down like this. So, you didn't know which was which. If you had a table named customer and a query named customer, you had to guess. Because they just assumed everyone had like a little TBL customers or something like that.

Anyways, so queries, customer LFQ, next. All right, now which fields do you want in the combo box? Customer ID's got to be first. Remember that. Always put your ID first. Now, pick either one of these. Which one do you want? Do you want last name first or first name last? Doesn't matter to me. I'll go last name first. Okay? Now bring that guy over. We don't need that one.

Next, we're going to sort by LF. That's our last name first. Next. Now this is what my columns are going to look like. Remember we had that checkbox up here a minute ago when we did the title combo box? That's because that was based on a table. And so Access easily was able to determine what the primary key was. Since this is based on a query, we don't get that checkbox. But all we have to do is just take the width of this column and just slide it to zero. That's it. That's all you got to do. And it does the same exact thing. That's all that checkbox does when it hides it.

Next. All right, now we're asked another question here. Which field do you want to be bound to the value in the combo box? In other words, what's the actual value that you're picking? We're not really picking the last name, first name. We're picking the customer ID. So, that's going to be the bound column. Next.

Now, do you want to remember the value for later use or store the value in this field? We're going to store that in the customer ID. We're picking a customer from a list of customers that comes from the customer table originally and then it comes into our query so we can add them together, but it comes from the customer table.
Then we're going to store that customer ID in the customer ID field in the service table. See? That makes our relationship. Next, what label do you want for it? All right, customer. And then finish. And there's our customer ID. Slide it over. Make it nice and wide. Do a little format paint, and there we go. Save it, close it, and then open it up. There's my service form. Now I can pick a customer from a list of customers instead of having to type in Will Riker.

Now one thing I am going to bring up is since we added this combo box, it goes to the end of the tab order. Remember tab order? If you're not familiar with tab order, go watch my video on tab order, or go watch my Access Beginner 1 class. All right, the tab order - go to Design View - is right up here. And this is the order that the fields come in. All right, so it had service ID, service date, description, notes, and then combo 9.

Now combo 9 is that combo box we just created, the customer ID. I don't like that it's called combo 9. I can't tell what it is. So let's change that to say customer combo. All right, so hit cancel. And before I show you that, quick advertisement. I got to sneak a little advertisement in once in a while.

Okay. If you want to learn more about combo boxes, relationships, all that stuff, check out my Access Expert Level 1 class. We go over all kinds of different types of relationships, primary and foreign keys, ad hoc query joins, relational combo boxes, all kinds of extra stuff. I got tons and tons of lessons on my website. Here's the link down here. I'll also put a link down below. Come and check it out.

Okay, but open up the properties for this combo box and right there, the name, change that to customer combo. You could call it customer ID, but I like to call it customer combo so I know I'm looking at a combo box. All right, save it. Now we can go back into tab order, and now you can see that it's clearly there. I'm going to hit the auto order button that will automatically order them from top to bottom, hit OK. And now when I close it and open it back up again. Oh, save changes. Yep. Now you can see I can go tab, tab, tab, tab. All right. I could put in here, you know, lost, fix his phaser, you know, blah, blah, blah, whatever else. All right. Go to the next service call, pick a customer. All right. Jim Kirk, right. Eight, order, that kind of stuff. So there is my relational combo box.

And the benefit of this is I can use this customer combo box everywhere. In fact, if you happen to watch my invoicing video, which is like part three of the TechHelp Free template, I didn't tell you to watch it ahead of time because I was going to go ahead and explain it now. But in part three, it goes the TechHelp Free blank database, then contact management, where we add in contacts, right, contacts for each person. Then part three we add in orders. And you can see right there I've already got a combo box that does the same thing. And it's the same set of data.

So if I add another customer, all right, let's call this guy Mr. New Guy, okay, and I close this and I go back to my service form, drop it down, look at that, new guy right there in alphabetical order. All right, that's the benefit. And he's also now on the order form, see? That's why you use a relational combo box. You don't have to update this combo box everywhere.

Now what if you're working on the customer form, let's say, and you drop this down and the title that you want isn't in there? Maybe you want master or father or something else, some other kind of prefix title, right? Can you add to this box without having to leave this record and go back to the table and then close this form and then reopen it? Yes, you can. I will show you how to do that in the extended cut for the members.

All right, let's say for me, for example, I want to put brother in here, or reverend. Let's put reverend. I'm the Reverend Richard Rost. I like alliteration. All right, now reverend's not on the list, so I've created something called a list items edit form. Click on that, and a little form pops up. See that? I can add it to the end, close it, and now it'll show up in the list. See? All right, that's one way to do it.

Another way to do it is something called the onNotInList event. And that involves a little bit of programming, six lines of code. It's not hard. Let's say I want to go with brother instead. Hit Tab, and Access says, brother is not on the list. Would you like to add it? Yeah, sure. And there we go. And now it's added. And now both of those things will show up in my table. All right, that's the list items edit form and the onNotInList event, and both of those things are covered.

16 minutes long, extended cut, silver members and up get access to all of my extended cut videos, and I think we're approaching 200 pretty soon, so there's a lot of extended cut videos to watch. So check it out. How do you become a member? Click the Join button below the video. After you click the Join button, you'll see a list of all the different types of membership levels that are available. Silver members and up will get access to all the extended cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. Platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website and not just for access. I also teach Word, Excel, Visual Basic, ASP, and lots more.

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. If you like this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select All to receive notifications when new videos are posted.

Click on the Show More link below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons, and lots more. YouTube no longer sends out email notifications when new videos are posted, so if you'd like to get an email every time I post a new video, click on the link to join my mailing list.

Now, if you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It's over 3 hours long. You can find it on my website or on my YouTube channel. And if you like level 1, level 2 is just $1. And it's also free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there.

Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com.

TOPICS:
Building a combo box in Microsoft Access
Setting combo box values from another table/query
Creating a relational combo box
Storing customer data in service forms using combo boxes
Using table design to create value lists
Adding titles to a title table for combo boxes
Editing the customer table to store title IDs
Creating a combo box in design view
Sorting values in a combo box
Making a combo box with hidden ID columns
Binding combo box values to a field
Using concatenation for combo box display
Creating a service table to log customer service data
Using concatenation to combine first and last names
Adding a customer combo box to a service form
Using tab order for efficient data entry in forms
Updating combo box values across multiple forms
Using a query for dynamic combo box values
Differentiating short text and long text fields in forms
Editing tab order for form fields
Adding dynamic combo box values on forms
Using the onNotInList event for combo boxes
Creating and using a list items edit form

COMMERCIAL:
In today's video from Access Learning Zone, I'll show you how to create a combo box in Microsoft Access that fetches its values from another table or query. We'll start by setting up a simple table and combo box for customer titles, then add those titles to a customer form.

Next, we’ll link the combo box to a customer ID, making your database more efficient. You'll also learn how to concatenate fields so you can combine first and last names in the combo box.

Finally, I'll guide you on adding the combo box to a service form and show you how to automatically update list values across multiple forms.

You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.