Combo Box Shows ID
By Richard Rost
12 months ago
Show Text Instead of IDs in Access Combo Boxes In this Microsoft Access tutorial, you'll learn how to adjust combo boxes to display text values instead of ID numbers, ensuring your forms present user-friendly information. By following this guide, you'll be able to make combo box configurations using queries and format them correctly for a seamless user experience. Cooper from Loveland, Ohio (a Platinum Member) asks: I created a combo box in my Microsoft Access form to let users select a customer name. The combo box pulls data from a table of customers, which includes IDs and names. For example, the table lists 1 - Kirk, 2 - Spock, and 3 - Bones. However, when I select a name in the combo box it displays the ID (like 1, 2, or 3) instead of the name (Kirk, Spock, or Bones). How can I adjust the combo box so that the names are displayed instead of the IDs? PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, Display Text Instead of ID in Combo Box, Microsoft Access Combo Box Fix, Relational Combo Box, Access Bound Column, Access Key Column, Access Combo Box Wizard, Access Query for Combo Box, Access Combo Box Properties, Combo Box Data Source, Access Format Combo Box, Access Column Width
Transcript
Today we're going to talk about what to do if you make a combo box and you see an ID there instead of the text that you're expecting to see. Right? Like a customer ID instead of the customer's name. Why does this happen, and how do you fix it? Today's question comes from Cooper in Loveland, Ohio, one of my platinum members. Cooper says, "I create a combo box in my Microsoft Access form to let users select the customer name. The combo box pulls data from a table of customers, which includes IDs and names. For example, the table lists, and I change the names. Don't ever send me real names—one Kirk, two Spock, three Bones. However, when I select the name in the combo box, it displays the ID like the one, two, or three instead of the name Kirk, Spock, Bones. How can I adjust the combo box so that the names are displayed instead of the IDs?"
Well, Cooper, this is a common problem. It happens a lot. One of the reasons I'm making this video is because I've seen this question a million times. Let's first talk about some prerequisites of things you should know before we continue.
First off, if you're not familiar with table relationships, like relating customers to orders or things like that, go watch my relationships video. This is one of those things that is foundational to Microsoft Access. If you're just used to Excel and working with spreadsheets, you've got to learn relationships to really get how to use Access. So definitely go watch this video if you haven't yet. And go watch this video on making relational combo boxes.
Alright, a relational combo box is a combo box that gets its list of values from another table or query. For example, on your order form, you're getting a list of customers from the customer table. Alright, so go watch both of these videos. They're free. They're on my YouTube channel. They're on my website. Go watch them, and then come on back.
Alright, here I am in my TechHelp free template. This is a free database you can grab off my website if you want to. And in this database, I've got customers, right? Here's my customer table, and I've got orders. And in the order table, we store the customer ID so we know which customer placed the order. Now, we don't save the name in here, just the ID. That's how relationships work. But when you go to the customer form and you go to their orders, you don't want to see customer ID one in here or whatever. You want to see the customer's name. It just gets more user-friendly. If you're adding a new order, you want to be able to pick a customer from your list, right? So that's what a relational combo box does.
Now, let's do this. Let's go and delete the combo box that's on here right now. I'm going to slide this over here. Alright, let's delete this combo box. I'm going to show you how this problem occurs. Alright, I don't have a combo box here yet. I'm going to create one. So I'm going to go up to Form Design. I'm going to grab a combo box right there. We're going to drop it out here on the form. And the wizard starts up.
Okay, you want to pick values from another table or query that's option one. Next. Where do you want to get the values for your combo box? Let's just say the customer table for now. Alright, customer table. Next. Alright, what fields do you want in your combo box? Well, we need the ID. That's got to be in there. That's what's making our relationship. That's the bound field. Okay? And let's bring in first name and last name. Alright. Next. What do you want to sort by? Alright, let's sort by first name and then last name. You can do it either way, whichever you prefer. Next.
Now, remember this screen. It says, "How wide would you like the columns for your combo box?" You can adjust these. You know, you don't want them too big, too small. But pay attention to this right here. It says, "Hide key column." What is that hide key column? Well, hide key column means that customer ID is in there because that's the value that we're actually storing in the order form in the order table. But we don't need to see it in the box. Now, if you turn this off, this will be the first visible column in the box, and you will see that. You'll see that number. So that could be the first reason why you might see that number there. Alright. But I'm going to hide that key column. Alright, then I'll hit next. And we're going to store that value. We're picking a customer. We're going to store the customer ID in the customer ID field of the order table. Next. What label do you want? Customers, fine. And we're done.
Alright. I'm not going to take time formatting it and all that. But now, if I save this and close it and then open it back up again, there you go. You can see there's my list of customers. Now, you're only seeing the first name in there. Why? Because these are separate columns. OK. You'll see the last name when you open the box up. But just like how the ID is hidden, well, this only displays one column at a time.
So what I do in my invoicing video is I make this query here called customer LFQ. This is a calculated query field. And what I do is I put together last name and first name. And it looks like that. Alright. I'll zoom in so you can see it better. Shift F2. Alright. I create a new field called LF. And that's last name, a comma space, and first name. So you get names like Rost, Richard, Kirk, James, and so on. So this is a new calculated field. And since this is one field, now this can be one column in a combo box, along with the ID, which is the bound field.
So now that I have this query, instead of using this combo box that only shows me one name, I can use that query to use for the data that goes in this combo box. It's the same data. But now I got last name and first name together in one field, which will look better in the box.
So let's rebuild this box again. OK. Let's go to Form Design, find our combo box, drop it back down here. Alright. Look up the value from a table or query. Next. This time, I want to go to queries and pick that customer LFQ that I made. Alright. Next. Now I want the ID and LF. That's my last name, first name field. OK. Next. How do you want to sort it? Sort it by LF. Next. OK.
Now, remember I told you to remember this screen. It says, "How wide would you like to make the columns for your combo box?" What's different? I saved it before in paint, so you can see it. Here's the new one. Notice what's different. Let me grab the pencil here. This thing right there. Hide key column. It's missing. It's not in the new one. Well, why is that? Well, this column only shows up if you're basing the combo box off of a table. If you're making it off of a query, you don't see that little checkbox there. But that's OK. You just have to know to make the width of this column zero. OK. And you can do that by simply grabbing that border right there and just dragging it all the way to the beginning. I like to go a little bit past it, so it makes sure it's zero. And there you go. And now that column is hidden.
If you don't do that, let me show you what happens. If you forget to do that, and you go next, and then we're going to bind customer ID and save that in the customer ID field. And we're going to put customer as the label. This is what you get. Save it, close it, open it, and oh, look at that. There's the ID, because we didn't make that column width zero. OK.
Now, if you've already done this, and you've already made the combo box, all is not lost. You can still fix it. All you have to do is go into the properties for this combo box. Go into the properties. Alright. The first thing you're going to do is give it a good name, because the combo box wizard doesn't let you name the combo box. It's one of my pet peeves about Access. So we're going to call this, and we're going to name it customer combo. That's the first thing we're going to change. OK. But then come over here to format.
Now, under format, you can see the column count is two. Remember, there were two fields in here, the ID and my LF field. And if you can't remember that, go over to data, and you'll see here is the SQL statement that makes up the list of stuff that's in the box. A little more advanced. But if you simply zoom in here, shift F2, you'll see we're selecting customer LFQ.customerid. That's the customer ID field, comma, and then the LF field from this query. That's all this is. It's a statement that basically says we're grabbing these fields from that query and sorting it by the LF field. But what's important to remember is we had two fields, two columns.
Well, here's the column width property. OK. Since we didn't make that column width zero, we can just simply come in here and do this. Boom. Make it zero. That's all you got to do. OK. And if you want to make this one wider now, because before we had one point something in one, we can make this guy two inches. And just make sure that this property down here is the same as those. So two, and that'll fit both of those just fine. OK. And now we're going to save it and close it and close it, and open it, and oh, look at that. It's fixed. And it still works. So if we change this to someone else, it'll still save that ID in the table, because the ID is the bound field.
That's another property you can see in here. If you go to data, you'll see the bound column is one, which means the first bit of data, which is the customer ID, is what is getting saved to the table that this thing is based on, which is, in this case, it's order Q, but it's the order table. OK. Makes sense?
Then all you got to do is fix this, resize it, and color it to make it all pretty. And then that's basically it. That's how you fix your combo box. The problem usually is most people get lost on this. If you base your combo box on a query, you will not see this. This hide key column. You just got to remember to hide it yourself.
If it's not on our list, Sammy, if it's not on our list for the Access team, that should be on there. If the wizard only sees one key column, it should definitely still give you this key column box. I think that's just so. And you really shouldn't have multiple IDs in your combo box to begin with. The first column should always be an ID, and that's it. That's how it should be done.
If you want to learn more about this stuff, the invoicing video is where I actually build the invoicing form with that combo box. You can check that out and learn more. And I cover calculated fields, including on forms and in queries, like I showed you in that customer LFQ, in this video. And if you're new to Access, make sure you check out my Access Beginner 1 class. It's absolutely free. And in Access Beginner 2, I do cover the beginning of relationships, like relating drivers to cars and things like that, relationships between your tables. So check that out.
And of course, in my expert level series, we really go into relational design concepts, types of relationships, relational combo boxes, normalization, referential integrity, all the cool, the cool kid database stuff is in my expert classes. And you'll find information on this on my website. I'll put links to all this stuff down below. I got tons of classes, folks. Tons of classes. If you want to learn Access, you're in the right place.
But that is going to do it. That's your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Combo box showing ID instead of text
Creating a combo box in Access
Combo box wizard setup
Hide key column in combo box
Using queries for combo box data
Creating calculated query fields
Displaying full names in combo box
Adjusting combo box column width
Editing combo box properties
Binding fields in combo box
Using multiple columns in combo box
Naming combo boxes in Access
Fixing visible ID issue in combo boxes
COMMERCIAL:
In today's video, we're tackling a common problem in Microsoft Access - when your combo box displays an ID instead of a name. We'll show you why this happens and guide you through fixing it step-by-step. You'll learn about relational combo boxes, how to set up fields correctly, and how to format them so you see names instead of IDs. I'll also explain key features like hiding the key column and utilizing a calculated query for better display options. Whether you're new to Access or just need a refresher, this tutorial has you covered. 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. Why might a combo box in Microsoft Access display an ID instead of the corresponding text?
A. The combo box was not set up to hide the key column.
B. The combo box was not created using the Form Design tool.
C. The combo box only supports displaying numeric data.
D. The combo box does not have a properly defined sorting order.
Q2. What is a relational combo box in Microsoft Access?
A. A combo box that gets its list of values from user input.
B. A combo box linked directly to a Microsoft Excel spreadsheet.
C. A combo box that draws values from another table or query.
D. A combo box using its own independent list of static choices.
Q3. What property should be adjusted to hide the customer ID in a combo box created from a query?
A. Change the combo box type to multichoice.
B. Adjust the column count to zero.
C. Drag the border of the column to make its width zero.
D. Set the combo box to read-only.
Q4. What does the "bound field" in a combo box refer to?
A. The first visible column in the combo box.
B. The field that is required to be displayed as default.
C. The field whose data is stored in the linked table when a selection is made.
D. The name label assigned to the combo box for user recognition.
Q5. If a combo box is built on a query, what must you remember to do that the wizard does not automatically prompt you for?
A. Provide a default value for the combo box.
B. Set the width of any key column to zero manually.
C. Create a backup of the query data.
D. Make sure all combo box values are sorted alphabetically.
Q6. How can you combine first and last names into one field to display in a combo box?
A. Use a static text field combined with macros.
B. Utilize a calculated field in a query.
C. Manually enter combined names into the combo box.
D. Create separate forms for first and last names.
Q7. Why is the "hide key column" option important for a combo box based on a table?
A. It prevents the combo box from storing incorrect data.
B. It ensures the combo box does not display redundant columns.
C. It hides the ID column so users see only meaningful text.
D. It restricts user access to certain database tables.
Answers: 1-A; 2-C; 3-C; 4-C; 5-B; 6-B; 7-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 Microsoft Access Learning Zone will help you address a common issue with combo boxes in your Access forms. Often, users expect to see descriptive text, like a customer's name, appear in a combo box, but find that numerical IDs are displayed instead. This can be confusing, so let's explore why it happens and how to remedy it.
Imagine you've set up a combo box on your Access form to let users select customer names. The combo box pulls data, including IDs and names, from your customer table—an essential part of maintaining relationships among tables in Access. Relationships allow you to use identifiers, like IDs, to connect various tables while avoiding data redundancy. If you're not familiar with this concept, I strongly recommend reviewing my introductory content on table relationships and creating relational combo boxes.
A relational combo box, in essence, fetches its list of options from another table or query. For example, while filling in an order form, you want to retrieve a list of customer names from the customer table. It's both logical and convenient. Let's examine how to create one and ensure it displays names, not IDs.
First, if there's already a problematic combo box on your form, the best approach is to delete it. Start from scratch—go to Form Design, select a combo box, and add it to the form. Proceed with the wizard, which will guide you through selecting values from another table or query. Choose your customer table, incorporate ID, first name, and last name fields into your combo box, and decide on a sorting order.
One crucial screen in the wizard involves adjusting column widths, and more importantly, the 'Hide key column' option. This option ensures that while the combo box relies on the customer ID for data relationships, the ID itself doesn't need to appear visibly. If this option isn't correctly set, IDs, unfortunately, will display.
If you've already created a combo box and need to correct it, don't worry. By accessing the combo box's properties, you can adjust the column width of the ID field to zero manually. Ensure the column count matches your data fields and optimize the width for user visibility. You'll find a property called 'bound column', ensuring the ID field is what gets saved in your data tables—this is crucial for data integrity.
For those who prefer showing both first and last names, consider creating a query with a calculated field that combines them into one. This can be a more user-friendly single column in your combo box. By basing your combo box on such a query, and taking care to set the ID column width to zero, you can streamline the user interface while maintaining the underlying data relationships.
If you're looking for more comprehensive guidance, I offer extensive tutorials on invoicing forms, relational combo boxes, and calculated fields across queries and forms. For beginners, my Access Beginner classes cover foundational concepts like relationships. For advanced users, my expert-level series delve into relational design, normalization, and more.
Visit my website for more resources and classes on Microsoft Access. A complete video tutorial with all the instructions discussed here is available there.
Live long and prosper, my friends.
Topic List
Combo box showing ID instead of text
Creating a combo box in Access
Combo box wizard setup
Hide key column in combo box
Using queries for combo box data
Creating calculated query fields
Displaying full names in combo box
Adjusting combo box column width
Editing combo box properties
Binding fields in combo box
Using multiple columns in combo box
Naming combo boxes in Access
Fixing visible ID issue in combo boxes
|
