USA First
By Richard Rost
19 months ago
Anchor Items at Top of a Combo Box in Microsoft Access In this Microsoft Access tutorial, you will learn how to anchor specific items, such as United States, Canada, and the UK, at the top of a combo box. This expert-level guide covers setting up a custom sort order, creating relational combo boxes, and essential database design practices for optimized data entry. Patrick from Hoffman Estates, Illinois (a Platinum Member) asks: My company does business in many different countries, and to keep the data proper, we have our employees pick from a list of countries instead of typing them in. Although we do leave in other options for those rare new countries that pop up, the vast majority of our customers are in the United States, Canada, and the UK. Is there any way to keep those three items at the top of the combo box rather than have my people have to either type or scroll all the way down the list? MembersIn the extended cut, we will learn how to hide the text box for typing in a country name unless the user selects 'Other' from the combo box. I will show you how to add separators in the combo box and update the invoice to reflect either the selected country name or any custom entry typed in. 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 CoursesMember Links
Keywords TechHelp Access, anchoring items in Access combo box, custom sort order Access, relational combo box Access, pre-sorted combo box values in Access, highlight specific items combo box Access, set default combo box values Access, advanced combo box techniques Access, prioritize items in Access combo box, dynamic sorting Access combo box, combo box design tips Access, Access combo box with custom order
Subscribe to USA First
Get notifications when this page is updated
Transcript
Today's topic: USA First. I can hear the people in the back room, USA! USA! No, this is not about patriotism. Although, here, I'll put the flag up. I do love my country. We're not perfect, but we're awesome.
Anyways, the goal of today's video is I'm going to teach you how to anchor one or more items at the top of a combo box or a list box. Right? If most of the people that you deal with are from the USA, you want them first in your combo box, and then all the rest of them can be in whatever order.
Today's question comes from Patrick in Hoffman Estates, Illinois, one of my Platinum members. Patrick says, "My company does business in many different countries and to keep the data proper we have our employees pick from a list of countries instead of typing them in." That's smart. Although we do leave another option for those rare new countries that pop up. Okay, you leave it like another. So you can type in if it's a weird... There's always a new weird country starting every other couple of years.
"But the vast majority of our customers are in the United States, Canada, and the UK. Me too. Is there any way to keep those three items at the top of the combo box rather than have my people either have to type or scroll all the way down the list?"
Yeah, I run into this a lot online when you want to pick United States and you gotta scroll all the way to the bottom and find it. Okay, yeah. So what we're gonna do is we're gonna set up a custom sort order for your countries so you can pick whatever order they appear in. Let me show you how to do that.
First of all, this is going to be an expert level video. What does expert mean? Well, expert is more than the basics but not quite programming. We don't need any developer VBA stuff to do this. Okay, so if you've never done any programming, don't worry about it. We won't need it today. But it is a little bit beyond the basics. You're going to need to know some things about relationships and building combo boxes.
Let's talk about the prerequisites real quick. You're definitely going to need to know about relationships, so go watch this video if you don't know how to relate data from two tables together. Specifically, we're going to build a relational combo box. We're going to set up a table with our list of countries in it and we're going to relate that to our customer table. So you need to understand how to make these relational combo boxes where you can pick a value from a different table. These are both free videos. They're on my website. They're on my YouTube channel. If you don't know those things, go watch those first, then come on back.
Alright, so how do we set this up? Here I am in my TechHelp free template. This is a free database. You can download it off my website if you want to. And in my customer table, we do have address fields, but I left country just open. It's just an open text field. You can type in whatever you want. To keep the data more normalized, sometimes it's a good idea really to have it where you can pick from a list of countries but still leave the option where you can type in any old new country that pops up or one of the lesser-known ones that you don't necessarily have in your table.
So what we're going to do is we're going to add a country ID in here. Alright, design view. Here's country, let's add country ID. That will be a number of type long integer and this will be a foreign key here that's going to point to a primary key in a country table that we still have to make. So let's bring this guy up here. I'm going to put that right there in front of country ID. Okay, and we'll deal with setting up this data in just a minute. There's the place where we can store our country ID.
Now let's set up a country table. So create table design. It's going to be a country ID. That's my auto number, the country name. Remember don't just use name. Name is a reserved word. You don't want to mess with those. If you don't know what reserved words are, I got videos on that. I'll put a link down below. Now in addition to that stuff, we're going to also put in here a custom sort order field. That'll be a number. Now you can start at zero, whatever value you want, but this will be the order in which the combo box will sort the records. So you can have the default zero, which means any items that you want at the top of the list just use lower numbers like negative one, negative two, negative three, and so on. Okay, and we'll see how this works in just a second.
Let's save this. This will be my country table. Primary key, yep. Alright, let's go into here and let's add some stuff in here. OK, now thinking I got sort order 0. You put your normal countries in here, whatever, anything new you add will be a 0, and we'll just have those all be alphabetized. But let's say we want the top three in the box to be the United States, and I'm going to make that a negative 3, and then Canada, negative 2, and then United Kingdom, negative one. And why not Australia too? I got a lot of Australia customers. That'll be, well see now we gotta go up again. So let's go negative four, negative three, negative two, negative one. I want those at the top of my list. Now everybody else can be zero.
Let me just go grab a list of countries and I'll paste them in here. Let me show you guys a little trick what I did here. I said to ChatGPT, I said give me a list of the top 20 countries ranked on GDP, except USA, UK, Canada, Australia, so I can copy and paste, plain text. So it gave me a list of countries. It numbered them though, I don't want them numbering, so I said plain text, no numbering. So here they are. And now I'm gonna copy these.
Now watch this when you paste stuff into Access. Let me show you a trick. That's what these TechHelp videos are all about, is tricks. If I click here, see how it's, I got the blinking cursor. If I hit paste now, it tries to paste them all into that one text box. If I zoom in, shift up two, there they all are. I don't want them all in that one text box. I want each one on a separate line. And a separate record. Alright, so cancel that. Let me cancel this, hit escape. The key is, once you copy it, don't click so you got a blinking cursor. Click on the border, on the edge, like right there. See how it's highlighted like that? That means that entire, let's call it a cell, that field is selected. Now if I paste, look at that. See that, it pasted them all in as individual records. That right there, folks, is worth the cost of admission. That little trick right there. If you want to paste a bunch of stuff in there and not have it go into the same cell, into the same field to use Excel terminology, then make sure the whole thing like that is selected.
Alright, so I got my top four here, let's say, and then I got everybody else down here, and let's put another in here. I want to make sure other is always at the bottom of the list, so let's put a 99 in there or whatever, something really high. Okay, and that'll be at the end of the list. Alright, that's all set. Close it. Now we're ready to make our combo box. Don't we need a query, you ask? No, not really. The combo box can handle the sorting. In fact, the combo box's sorting will override a query. If you want to use that list in some other form or whatever, sure, you can make a query, but we don't need it.
Let's go to my customer form where this thing's gonna live. I'm gonna come down here. Now I want to leave this country text box because I want to have a space for them to type in the other if they pick other. But I'm gonna add a combo box above that. So let's grab our thingy here. Where's combo box? Right there. Click and drag down. Yeah. Alright. How are we building this one less we're gonna get the values from a table or query? Next. Where is the data? The data is in the country table. That thing that we just made next. Let's bring in all the fields. Need the ID for the relationship. I want to see the name and the sort order. We're gonna hide I want it in here so we can sort out if we're gonna we're gonna hide it in it. Next.
Okay, how do you want to sort the data? Well first I want to sort by the sort order. And then after that, if the sort order is the same, like the zeros, sort by country name. Okay, so all those zeros in the middle will be alphabetical. Alright, next, here's what it's going to look like, the key column is hidden. And remember, if you don't see this, that means that your data is based on a query. You just have to manually hide that yourself by going like, er. We have a table based one, so we can close that. I don't want to see the sort order in the combo box, so I'm just going to go like that and make that guy zero width. Everybody else looks good.
Next. Alright, so we pick a value, we're picking a country ID, what are we going to do with it? We're going to store that in the country ID field in the customer table. That's how these relational combo boxes work. Next, what label do you want? Doesn't matter, I'm going to delete it and then hit finish. Okay? Let's delete this guy. Now I got two country labels. I'm going to take this one, I'm going to cut it out, snip, and I'm going to click on the combo box and paste it. That'll attach it to this. See how it's now attached to that one? Isn't that kind of cool? Okay. OK.
Let's slide this out like so. And I'm going to just tuck this right up underneath it. And you'll just have to teach your people that that's the other country box. And in the extended cut for the members, I'm going to show you how to hide that and only show it if they pick other up here. That's going to be pretty cool. Alright. So now, let's save this, close it, open it, and let's pick... Oh, look at that, how nice that is. United States, Canada, UK, Australia up top, and then all the rest of the middle ones are alphabetical, and then way down at the bottom, I gotta scroll this up so you can see it, way down at the bottom is my other.
Now, if this box is too big for you, you can change how many items appear here. Go back into design view, bring up the properties for this guy. And I'm glad I did that, because one of my pet peeves is that the wizard doesn't ask you for a name. It's combo30. I don't like that. I want this to be the country combo. We already have a country ID. Actually, no, we could make a country ID if we wanted to, but I like to have my combo boxes say combo on them for lots of different reasons.
But over here under format, I don't want 16 list rows. Let's go with eight. That should be enough to see the countries at the top. Alright, close it, close it, save it, close it. Open it up and now I got a much shorter, more manageable list. Alright, and your users can still type stuff in. If they know they want China, CH, boom, puts it right on China. If they know they want other, OTH, boom, and they can, oh, oh, it jumped me to the next record. Why is that? I like when this little stuff pops up because I can remind you of it.
These are the questions I get asked all the time. We added this control last. This combo box was the last thing on the form. So it's the end of the tab order. If I hit tab, it takes me to the next record. There's a couple of things you can do. Let me bring this back down where it belongs. The first thing we can do is just fix the tab order. So I'm going to come in here and I'm going to come up to tab order and find my combo, which is way at the end. We're going to click on it and slide this up in front of country. Okay, so that should put it where it belongs in the tab order.
And another thing that I like, and this is completely dependent upon you and your data entry and how you like it. I'm going to go into other and set the cycle to current record. I'm not a big fan of tabbing off of one record onto another one. I don't know. But it all depends on your data entry. I like it so that when I'm done with a particular field on a record, it goes back to the top. That's just how I like it. It goes to the next record if I go to the next record. That's me, that's my personal thing, OK?
But there you go. Couple little other things we have to do now to work with the data that we already have in the database, we gotta make a few changes. Now, the way that I had it before for my customers, as far as country is concerned, I would just leave the country blank if it was United States. But now we've got to go and put ones in here. Is it one? Let's see. Country, one is United States. Yeah, ones for all the current people who are blank. So we want to go one, one, one. Not you for France. One, one, one. And if you've got thousands of these records, you're going to use an update query to do this.
Alright, an update query. If you want to learn about update queries, I've got videos on that, too. I'll put a link down below. Alright, I'm pretty sure Canada's 2. Let's see, country, Canada's 2, yep. So these will be replaced with 2s. And you can get rid of these now. We don't need anything in the other. OK, and then France, let's go find France. There you are, 32. We'll put a 32 here. Now for new records, that new record, you can see the country ID is coming in here as 0. You might want to make the default value now for this table 1, so that new people that come in default to 1. So let's go to Design View, set the country ID default value to 1. So they're USA for new records. So if I come into the form now, and I go to a new record, the default is United States. OK?
Now, lots to do still in the extended cut for the members. We've got a couple things we're going to do. First, we're going to hide the extra little box to type in a country name unless the user picks other. Then we'll show the box, and they can type it in. If they pick an existing one, there's no reason for that box. So we'll hide that. We're gonna put a separator here, just a bunch of dashes, but you can put whatever you want there. You can put lines or O's or X's or whatever, I don't know. Then we're gonna fix the invoice, because right now our invoice and any other reports or queries or forms that you use are based on that text country field.
So I'm gonna show you how with some little query magic, we can use either the country name from the country table or whatever's typed into the text field, depending on the value of this guy. If they picked other, use that. If not, use that. That would be tricky, but we'll do it. And that's gonna be in the extended cut for the members. Silver members and up get access to all of my extended cut videos. Not just this one, all of them that I've ever made. There's like 700 something TechHelp videos now so there's a lot of them. Gold members can download these databases and you get my code vault and all kinds of cool stuff. And of course, if you like learning with me come to my website, check it out. I got lots of lessons, lots of Access stuff. I live and breathe Access, guys. It's my thing. It's what I do.
But 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.
Want to learn more? Click the show more link below the video to find additional resources and links. YouTube does a pretty good job of hiding it. It's right down there. See this part of the description here. The name, the videos up here. There's a little show more down there, right down the bottom. It's kind of hard to find. But once you click on that, you'll see a list of other videos, additional information related to the current topic, lessons, and lots more. And YouTube no longer sends out email notifications when new videos are posted like they used to do. But if you'd like to get an email every time I post a new video, click on the link to join my mailing list, and you can pick how frequently to get emails from me, either as they happen, daily, weekly, or monthly.
Now if you'd like to become a paid member of my channel and receive all kinds of awesome perks, click on the join button. You'll see a list of all the different membership levels that are available, each with its own special perks, including my extended cut videos, access to my code vault, lots of VBA source code in there, template downloads and lots more. I'll talk more about these perks at the end of the video. Even if you don't want to commit to becoming a paid member and you'd like to help support my work, please feel free to click on the tip jar link. Your patronage is greatly appreciated and will help keep these free videos coming. I got some puppies to feed. But don't worry, no matter what, 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.
Now if you really want to learn Access and you haven't tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access including building forms, queries, reports and more. It's over 4 hours long. You can find it on my website or on my YouTube channel. I'll put a link down below you can click on. And did I mention it's completely free? The whole thing, free, 4 hours. Go watch it.
And okay, okay, a lot of you have told me that you don't have time to sit through a four hour course. So I do now have a quicker Microsoft Access for Beginners video that covers all the basics faster in about 30 minutes. And no, I didn't just put the video on fast forward. But I'll put a link to this down below as well.
Now if you like level one, level two is just a dollar. That's it, one dollar. That's another whole like 90-minute course. Level two is also free
TOPICS:
Combo box item anchoring
Creating a custom sort order
Setting up a relational combo box
Designing a countries table
Adding a country ID field
Creating a foreign key relationship
Custom sort order field configuration
Populating the countries table
Using sort order for combo box sorting
Handling unlisted countries in combo box
Customizing combo box properties
Setting default values for new records
Fixing tab order issues
Adjusting form's cycle setting
Updating existing records
Designing combo box to work with existing data
COMMERCIAL:
In today's video from Access Learning Zone, I'll show you how to keep USA, Canada, and UK at the top of your combo box in Access. We start by setting up a custom sort order for your list of countries. I'll demonstrate how to create a relational combo box, set up a country table with a custom sort order field, and properly configure your forms. Plus, I'll share a nifty trick for pasting bulk data into Access without it all ending up in one field. By the end, your most frequently used countries will be easily accessible at the top of your combo box. 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. What is the main focus of the video tutorial?
A. Setting up patriotic themes in combo boxes.
B. Anchoring specific items at the top of a combo box or list box.
C. Learning advanced VBA programming for sorting.
D. Creating a new database for country information.
Q2. Who sent the question that prompted this video tutorial?
A. Mark from New York.
B. Jennifer from Chicago.
C. Patrick from Hoffman Estates, Illinois.
D. Susan from San Francisco.
Q3. What skill level is required for following the tutorial?
A. Basic understanding of database creation.
B. Intermediate level requiring some programming skills.
C. Expert level, but not requiring VBA programming.
D. Advanced level with extensive programming knowledge.
Q4. Which three countries does Patrick want to keep at the top of the combo box?
A. USA, Mexico, Canada.
B. USA, France, Italy.
C. USA, Canada, UK.
D. USA, Germany, Japan.
Q5. What is the purpose of adding a country ID in the customer table?
A. To personalize customer greetings.
B. To establish a foreign key relationship with the country table.
C. To include additional customer metadata.
D. To expedite data entry for addresses.
Q6. What should the custom sort order field in the country table help achieve?
A. Determine the alphabetical order of countries.
B. Enable filtering of countries based on population.
C. Specify the order in which countries appear in the combo box.
D. Track the number of times a country is selected.
Q7. How did the instructor obtain a list of countries for his demo?
A. Copied directly from a manual.
B. Extracted from an online database.
C. Generated using ChatGPT.
D. Typed manually from memory.
Q8. How can you ensure multiple country names are pasted as separate records in Access?
A. Paste them into a text field directly.
B. Click on the border of the target field before pasting.
C. Use a special Access import feature.
D. Paste them into a table and then distribute automatically.
Q9. What is a recommended adjustment for ensuring "Other" always stays at the bottom of the combo box list?
A. Set its custom sort order to the highest negative value.
B. Set a very high positive value for its custom sort order.
C. Alphabetically place it at the end of the list.
D. Adjust it manually each time new data is entered.
Q10. What was the correction made for the tab order of the newly added combo box?
A. The combo box was duplicated.
B. The tab order was adjusted to place the combo box before the text box.
C. The combo box was given a higher ID number.
D. The text box was removed entirely from the form.
Q11. What additional feature is being shown in the extended cut for members?
A. Adding more countries to the combo box.
B. Creating a new form for country selection.
C. Hiding the extra text box unless "Other" is selected.
D. Exporting the list of countries to another database.
Q12. How can you set the default value for new records to "United States"?
A. By manually entering "United States" for each new record.
B. By setting the default value of the country text field.
C. By setting the default value of the country ID to 1.
D. By creating a macro to automatically enter "United States".
Q13. What does the instructor prefer in terms of tabbing between records?
A. Tabbing off one record onto another one.
B. Tabbing within the current record only.
C. Tabbing directly to the end of the record set.
D. Tabbing multiple fields at once for faster entry.
Answers: 1-B; 2-C; 3-C; 4-C; 5-B; 6-C; 7-C; 8-B; 9-B; 10-B; 11-C; 12-C; 13-B
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.
|
