|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Cascading Combo Boxes By Richard Rost 
5 years ago
Cascading Combo Boxes: One Box Filters Another In this video, I will show you how to set up cascading combo boxes. This is where the value selected in the first combo box will filter the results of the second combo box. For example, you choose a state in box one, and then box two will show only a list of cities from that state. Liana from Honolulu, Hawaii (a Platinum Member) asks: When I'm entering in my customer's address, is there any way that I can pick the state first from a list of options, and then have the city combo box only show cities from that state? I have a very select list of city/state combinations that my company sells to, and it would be so much easier than typing these in every time. Thanks! MembersMembers will learn how to display cascading combo boxes in a continuous form, and also how to perform the cascade without the need of an external query.
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!
Links
Watch Next...If you want to see another example of cascading combo boxes, watch my Vehicle Maintenance video. I show how to do the same thing with make and model for a car. Pick "Ford" and then only Ford models will show up in the model combo box; Keywordsmicrosoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, cascading combo boxes, city state select, link combo boxes, dependent on another combobox, multiple cascading combo boxes, cascading combo boxes in continuous forms, cascading combo boxes subform, choose from list of cities in state, afterupdate, me.requery, oncurrent, List Items Edit Form
IntroIn this video, I will show you how to set up cascading combo boxes in Microsoft Access, where selecting a value in the first combo box filters the choices available in the second. You'll see how to design related tables for states and cities, update your customer form to use these combo boxes, create the necessary queries, and add simple VBA code so the city list updates automatically when the state is changed or when moving through records. I will also cover how to build forms to easily manage your lists of states and cities and link them for editing right from the combo box.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, I am going to show you how to set up cascading combo boxes. This is where the value selected in the first combo box will filter the results of the second combo box. For example, you can choose a state in box 1 and then box 2 will only show a list of cities from that state. Today's question comes from Lianna in Honolulu, Hawaii, one of my platinum members. Lianna says, when I am entering my customer's address, is there a way that I can pick the state first from a list of options, then have the city combo box only show cities from that state? I have a very select list of city-state combinations that my company sells to, and it would be so much easier than typing these in every time. Thanks. Yes, Lianna, we can definitely do this with something called cascading combo boxes. That is where you pick an option in combo box 1, and based on that box, it filters the list in combo box 2. Let me show you how to do it. Before we do this the right way, I have six other videos I want to make sure you watch first, so you understand all the concepts I am about to cover. First, you should understand relationships between two tables. That is essential. And how to make relational combo boxes. That is where a combo box picks a value from a different table than the one you are working on in the current form. You should know how to get criteria from an open form to put that in a query. And to do this right, we are going to need one line of VBA code. We are going to have to put it in the After Update event and the On Current event. If you have not watched my Intro to VBA class, go watch that now. Pause this video, go watch that. But even if you have never done any VBA before, do not be scared. It is just one line of code, and I am going to show you exactly where to put it. Here I am in my TechHelp free template. This is a free download. You can go grab a copy from my website if you want to. I will put a link down below in the links section, where you can also find links to all the videos I just mentioned in the prerequisites. Those are all free, so go watch them. In my customer table, we have city and state as text fields, and the user can type in whatever they want. If you go into the customer form, you can just type in the city and the state right here. But Lianna wants a little more control. So we are going to make a list of states and a list of cities, and we are going to create tables for those. Then we are going to edit our customer table to only allow the user to pick from those pre-approved cities and states. Let us start off by creating a state table. Create table design and let us make this a little bit smaller. We will start off with a state ID. That will be our autonumber, a state abbreviation (why is abbreviation such a long word? I have no idea), and a state name. Let us make this our primary key. Can you do this without an ID? Yes, you can. I do not like doing that, though. I like having IDs in pretty much every one of my tables. You can use state abbreviation as a primary key if you really want to, but that is not my preferred way to do it. Save this as StateT. That is my state table. And let us put some values in it. We have Florida, New York, California, and one more, how about Texas? Okay, so these are the states that Lianna sells to. Of course, she is from Hawaii, so let us put Hawaii in there. HI for Hawaii. Now let us make a city table. Create table design. Here we are going to have our city ID, that is again our autonumber, a state ID, that will be a number of type long integer (that is our foreign key that points back to the state), and then the city name. That will be our text field. This is our primary key. Save this as CityT. Now let us open up the state table so we can see what the states are, what their IDs are, so we can fill in some cities. Of course, you can make a nice form with a subform to be able to do this and you do not have to use the tables like this. Maybe I will show you how to do that at the end of the class. So let us put our list of stuff in. State ID first. First state, that is Florida, city: Miami. Same state, Florida: I am in Fort Myers. One more: let us do Fort Lauderdale. I will do where I used to live too. I used to live in Cape Coral. Those are all cities that we sell to in Florida. Next, New York: I used to live in Buffalo. Then, let us do Rochester and Syracuse. California, we will just do Los Angeles. Let us say we always sell to Los Angeles. For Texas, we will do Houston. I also used to live in Corpus Christi. And for five, we will do Honolulu. So there is our list of pre-approved states and cities. See how that works? There is the relationship right there between state ID. Of course, you watched the relationships and the relationship combo videos. Now let us go into our customer table and we will make it so instead of storing text for these things, we are storing IDs. It gives us more control over the data. So let us go into our customer table. Right click and then design view. If you have a bunch of data in here already and you do not want to lose the cities and states you already have, you have to use some update queries and change that over. What I would do is I am going to add CityID on the bottom down here. That is going to be a number, and then StateID. That will also be a number. I am going to grab these two together and slide them up. Put them right there. Now what you will have to do is use some update queries and say, okay, if this is Buffalo, change it to CityID 3 or whatever that happens to be. I have a whole separate set of videos on update queries. Go look for those on my website if you want to. But I am just going to say this is a new database, so I am just going to get rid of city and state. Are you sure? Yes. Now I have to come in and fill in all the cities and states for my people. Let us go to the customer form. We are going to get rid of city and state down here. Right click, design view. Goodbye. Slide you down. Zip code, you are going to go right down here. Now I am going to make combo boxes to pick the state first, then the city. I know it seems backwards, but that is the way you want to do it. That is the way it looks better on the form too. I have a whole separate template, by the way, that I call the zip code lookup template. I have other customers that have asked me, "Hey, I just want to be able to type in the zip code and have Access automatically populate the city and the state." If you are looking for that, I have a zip code template on my website. Half of it is free and then there is some more extended stuff that covers how to do things like this. You type in the zip code and then it will pre-populate the city and the state. In the extended version, you can have multiple cities and states that are in the same zip code, which happened when I was growing up. There were a couple of different cities, basically, that were in the same zip code. Check this out if you want to learn how to do this instead. But for our lesson, we are going to make a combo box right here. We will start with state. This is the relational combo box. You should have watched that video too. You want the combo box to get the value from another table or query. StateT is where we are getting them from. Bring over all three fields, that is fine. The ID is the bound field. Then we will see these two in the box. Next, sort by whatever you want. I will sort by abbreviation. Next, this is what it is going to look like. Slide that to about there. Maybe bring that smaller. There we go. Hide key column, that is fine. That is the ID. Access knows this is the primary key field, so it hides it for us. We are going to store that value in the StateID. We are picking a state from the list of states, we are storing it in the StateID. Next, what label would you like? State. Finish. There it is. I am going to Format Paint, grab some black. I put the Format Painter up here in my quick launch toolbar. Let us widen this out a little bit. I think we are going to need to make a little more room down here. Of course, we will need a label for zip code. Paste it there. Zip. Or if you are in a different country and you have a postal code or whatever you use. Let us see this in action now. Close this, save it. Open up the customer form. Here is my list of states. See that? Nice and easy. Florida. If you want the full text in there, that is fine. Just reverse the order that you put them in the combo box. Now, to get a list of cities just from this state, we are going to make a query. When the query runs, it is going to read this value. In fact, let us give this combo box a good name because the wizard does not. It comes in as Combo30. I hate that. Let us make this custStateCombo. Or StateID if you want to. I like to call my combo boxes Combo so I know how to treat them differently sometimes. So we have StateCombo, which is on the CustomerF. So it is Forms!CustomerF!StateCombo. Close that. Open it back up again. Now let us make a query that reads that data. This is why I wanted you to watch the video where you get a criteria for a query from a different open form. Go to Queries. Pull in the city table. Pull in the CityID, the CityName, and then the StateID. But we do not need to see the StateID in the query so we will hide it right there. For criteria, go right down to the criteria row and put in here: Forms!CustomerF!StateCombo. Just like that. Access will put the little brackets around there for me because I do not have spaces in my field names. That is Access Beginner Level 1. Do not put spaces in your field names or your table names or your query names or your form names. Save this as MyCityQ. This is my city query. Now close that and open up the city query. Watch what happens. Look at that. Buffalo, Rochester, Syracuse. That looks good. Let us go to a different customer. Let us pick Florida. And now I will run the query. Look at that. There are the Florida cities. See how that works? Now we are going to use this to power this combo box that we are going to add next for the city. It will read that value. Go to design view. Grab another combo box. Drop it right here. The problem we are going to have is while this wizard is running, the query cannot get a value because the form is in design view, so there is no value for it. We are going to see some errors in a second, but just ignore them. I will show you what to do. Get the values from a table or query. This time go to queries. Pick that CityQ we just made. Bring in both of those fields. Next. Sort by CityName. Next. Now that is why we are getting #Name errors, because it cannot read a value, so the query is not returning any records. But CityID, CityName, that is fine. I do not want to see the CityID, and since this is based on a query, you do not get that check box up here to hide that field. That is okay. Just take the width and shrink it down to zero. The value is still there, we just do not want to see it. That is our first hidden column in the combo box. That is the bound column. That is the value that is actually stored in the table. Here, we will see some actual data in it once we can run the query because this form will be open. Hit Next. What is the bound field? CityID. What do you want to do with it? We are going to store that value in the CityID in the customer table. Here we go. Next. What label do you want? City. Finish. Slide that right up. Slide that across. Let us do some formatting. Where is the Format Pane? There it is right there. Click. We have state, city, zip. Let us save it. Close it. Open it back up again. There is New York. Drop this down. Look at that. Buffalo, Rochester, Syracuse. See? That works fantastic. But we have a little bit of a problem. Watch this. If I change the state, it still says Buffalo, and I still have my list of Buffalo cities. What happens is this combo box, the City combo box, only evaluates the records that go in it when the form loads. If I change this to Florida now, and then close this, and then reopen it, now I will see a list of the Florida cities. But we do not want that. We need this combo box to refresh or requery itself, get a fresh list of cities, in two instances. One, whenever this box is changed; or two, when I move from record to record. I need the After Update event for this state box, and the On Current event for the form. That is where we are going to put our one line of code. Ready? We will do the state first. Double click on this guy. This is where the Intro to VBA class comes in handy. Go to events, go to After Update. Click the builder, that will bring up your VBA editor. If you get the little window that says what editor do you want, pick the code builder. Come right in here. We did not name the box first. I want to back up one little bit. CityID is Combo33. We named the StateID. We forgot to name this one. I hate that. Come up here, find the name, change Combo33 or whatever yours is to CityCombo. Now it has a good name. Now we can play with it. Come back over here to the code editor. All you have to say is one line of code: CityCombo.Requery. That is it. So now, when the StateCombo changes, the CityCombo will reload its list of cities. Save that. We have to put that in one other place too. Go to the form properties. Events, On Current. On Current happens when you move from one record to another, including when the form opens, because it loads up the first record. Click the builder. Now we are in the form current event. Same one line of code: CityCombo.Requery. Save it. Come out here, close this form, open it back up again. Florida, Cape Coral. Now if I drop this down and change to California, this box now has just Los Angeles in it. See that? Now if I move to another record, Florida, we did not pick a city before, I pick Fort Lauderdale. Let us change it. Let us go to New York. Now I can pick Buffalo. See? As I move from record to record, those boxes requery with the list of cities from that state. Texas, Corpus Christi. Here is a New Yorker from Rochester. As I go back through the records, look. See how nice that is? Much, much easier, and much better than having the user type this stuff in. Because then you get misspellings, especially if you want to do queries that are dependent on the city. If you want to do sales for a particular city or regions, or whatever else you divide them up by, the user is forced to pick from this list here. They cannot just type it in, or they might type it in wrong. They might spell Los Angeles wrong. Or they might come in here and do Hawaii, and of course spell Honolulu wrong. You do not have to worry about that stuff, spelling states wrong. I do not know how many times I misspelled Mississippi as a kid. Even though there is a little fun, "MISS, ISS," I would still misspell it. This allows you to control what your users can enter. Now at the beginning of class, I said if you were good students, I would show you how to make a little button where you can edit this list. If the user is in here, you go to New York and you drop this down, and you are looking for your New York city, for example, and that is not in this list, you can quickly hit a button and add it to it. If you want your users to be able to do that, I get it. Sometimes you do, sometimes you do not. If you guys are good students, I will show you how to do it. You have been good students. No one has been disruptive in class, so I will show you how to do it. But first, a brief advertisement. If you like the training that you are getting, if you like these videos, come check out more. I have lots more on my website. I have a whole series, 32 levels of expert stuff that goes through different functions, form design, report design, all that stuff. If you want to get into programming, VBA programming, which is really cool stuff, it takes your databases to a whole other level, I have a whole series, right now up to 31 developer classes. So lots and lots of stuff on my website. Come check it out, 599cd.com. Let us make a form where we can edit the cities and states. Start off with the state list. We are going to create - actually, I already have in my template here my sample forms, single and continuous. I am going to use a single form for the states and then a continuous form for the cities inside that states. I am going to take my single form here, copy, and then paste and make a copy of it. We are going to call this MyStateF, my state form. Open that up in design mode. Bind this guy to the state table. Now I can bring in existing fields, just like these: click, drag, and drop. The only reason I keep these around here is for the Format Painter. Watch: click, Format Paint, boom. This guy, double click, Format Paint, Format Paint. It just sets the colors and the stuff the way I like them. I like to have my IDs gray, so users know they cannot change that. StateID, we will change our labels a little bit here: the abbreviation and then the state name. Just like that, you can make this one nice and small if you want to and make the other one bigger, however you want it to look. We will make our list of cities go right down below it down here. Save this. Now let us make a continuous form for the cities. Here is my continuous form: copy, paste (Control-C, Control-V). Make this our CityF. CityF is right here. Design view. See, I have these templates all set up in the blank database template. That is where I make this TechHelp free template. Go watch that video too. I will put a link to it down below. Same thing. Let us bind this form. You do not have to reinvent the wheel every time if you have your template forms already set up the way you like them. Data, Record Source: this is going to be my CityT. Add existing fields. I am going to bring in just the CityID and the CityName. Do not worry about the StateID. The relationship will be created by the subform. If you have not built subforms before, I have more videos on that. I will put a link down below to my subforms video. Go watch that. Lots of good stuff. I have almost 200 TechHelp videos. You can spend over 100 hours just watching my free videos on my website and on YouTube. Lots of stuff you can learn. And imagine how much more I have on my website in the advanced stuff. Same thing here. Click, paste. I think this guy is good. CityID can go there. Honestly, on this form, we do not even need to see the ID. Just get rid of that. Get rid of that. Put the CityName here. Because the relationship will be - let us get rid of this stuff too. Delete. Slide that up. Slide that up. Slide that that way. Slide that up. All we really need to see is the CityName. Save it. Because the relationship will be formed between the parent and the child form based on the ID. All you have to do is take the state form (design view), take the city form, click and drag it, and drop it right there. In the subform's properties, there is this thing right here called Link Master Fields, Link Child Fields. It saw the StateID in both of these tables, so it made that relationship for you. Do this, and then we will copy and paste the label down here and put "Cities" like this. You can make this as big as you want, like that. Save it. Close it. Open up the state form, and there you go. There is your state with the cities below. Go to the next one: New York, California, Texas, Hawaii. Go down here, easily add another one. Now, I am going to show you how to pop this guy open from inside the other combo boxes. If you drop in the town, you are like, oh, wait a minute, I need Pennsylvania in here too. There is a way to do that. I am going to show you. Are you sure? I am going to show you. It is kind of cool, actually. One more quick little advertisement: the technique I am going to show you is something that I cover in my Access Beginner Level 8 class. It is called a Value List Edit Form. It is this guy right here. I do a lead source like "where did you hear about us?" We are going to click a little button, and it is going to pop open a form where you can edit the list right from inside the combo box. Of course, I cover lots more stuff in this class too. Access Beginner Level 8. So how do you do it? Go into your customer form, design view. Go into the properties for the combo box. Under Data, find the List Items Edit Form. Drop this down and pick that StateF. Close it. We can use the same edit form for both of these combo boxes. Do the same thing here: StateF. Close it. Save changes? Yes. Customer form. Drop this box down. Now notice we have this guy pop up. Edit List Items. Click on that. This guy pops up. You can reposition it if you want to. Move it over there and save it. If you want to add a state, just go to the end over here, add a state. PA, Pennsylvania. Put your cities in: Pittsburgh, Philadelphia. Now when you close it, these boxes will be refreshed. Now look at that, Pennsylvania is right there. Drop that down, you can pick Philadelphia. See how easy it is now to add items. Same thing, I want to add an item to this. Come in here, find that one, put another city in here: Harrisburg. Close it. There you go. Nice and easy. If you want to learn more, 10-minute extended cut, I cover how to do the same thing but in a continuous form. If you have a list form like your customer list form and you want to have the cascading combo boxes on that form, it requires a different technique that I show in the extended cut. Plus, no query required. We do not have to make that CityQ. I will show you how to do the whole thing in SQL. That is all covered in the extended cut for members. Silver members and up get access to all of my extended cut videos. Gold members can download the databases that I make in my TechHelp videos. How do you become a member? Click the Join button below the video. After you click the Join button, you will see a list of all the different types of membership levels that are available. Silver members and up get access to all of 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 do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more. If you liked 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 will 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 would like to get an email every time I post a new video, click on the link to join my mailing list. 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 is over three hours long. You can find it on my website or on my YouTube channel. If you like level 1, level 2 is just one dollar. It is 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. QuizQ1. What is the primary purpose of cascading combo boxes in Microsoft Access?A. To automatically update all fields in a form B. To filter the options in one combo box based on the selection in another C. To allow users to edit all records simultaneously D. To enable users to enter free text instead of choosing from a list Q2. In the example provided, what is the first step in setting up the cascading combo boxes? A. Creating a city lookup query B. Editing the customer form to include text boxes C. Creating separate tables for states and cities D. Writing VBA code to link the fields Q3. Why is it preferable to use an AutoNumber ID as the primary key in the State and City tables? A. It makes the database slower B. It restricts user access C. It ensures unique identification and easier relationships D. It prevents users from entering new data Q4. What is the purpose of adding StateID as a foreign key in the City table? A. To specify the population of the city B. To create a relationship between each city and its state C. To track the zip code for each city D. To store duplicate data for audit purposes Q5. How should existing free-text city and state fields be handled when converting the customer table to use IDs? A. Leave them as free-text only B. Use update queries to convert old values to IDs C. Delete all customer records and start from scratch D. Export them to Excel Q6. What is the main advantage of using combo boxes for city and state selection on a form? A. Allows users to enter any value they want B. Prevents misspellings and enforces data consistency C. Hides all other form fields D. Automatically sorts all records Q7. After creating the State combo box on the customer form, what is the next essential step to set up the cascading effect for the City combo box? A. Connect the customer form to the city table directly B. Make a query that lists cities filtered by the selected state C. Delete all existing cities D. Change the combo box type to list box Q8. In the city combo box, why is the CityID column width set to zero? A. To completely hide the combo box B. So users do not see the underlying bound value, only the city name C. To prevent the combo box from updating values D. To display the full list of city IDs to users Q9. When is it necessary to requery the City combo box? A. Only when the database opens B. When the State combo box value changes or the form moves to a new record C. Every time the city table is edited in the backend D. When a report is generated Q10. What VBA code is added to refresh the City combo box after the State selection changes? A. CityCombo.RefreshList B. CityCombo.UpdateList C. CityCombo.Requery D. StateCombo.Refresh Q11. Where should the CityCombo.Requery code be placed? A. Only in the form's On Open event B. In both the State combo box's After Update event and the form's On Current event C. In the database's macro D. In the city table design Q12. What is the advantage of using an edit form for list items in combo boxes? A. Allows users to delete all records quickly B. Lets users add to or edit the list of values on demand from within the form C. Prevents users from making any changes D. Disables the combo box entirely Q13. How does Access determine how to link the master and child forms when you create a subform for cities inside the state form? A. By searching for matching autonumber fields B. By default, looking for matching field names like StateID in both tables C. By looking for city names only D. By requiring manual table joins every time Q14. What is a major benefit of limiting user input to pre-approved lists using combo boxes? A. All forms will be locked completely B. Users can submit records faster, regardless of accuracy C. It enforces data integrity and reduces data entry errors D. It makes the database inaccessible offline Q15. What Access property allows you to assign an edit form to a combo box for editing its items? A. Row Source Type B. Default Value C. List Items Edit Form D. Control Source Answers: 1-B; 2-C; 3-C; 4-B; 5-B; 6-B; 7-B; 8-B; 9-B; 10-C; 11-B; 12-B; 13-B; 14-C; 15-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. SummaryToday's video from Access Learning Zone covers how to create cascading combo boxes in Microsoft Access. Cascading combo boxes allow you to filter the selections in a second combo box based on the value selected in the first. For example, after choosing a state in the first box, only those cities associated with that state appear in the second.This lesson was inspired by a question about entering customer addresses. Specifically, is there a way to first pick the state from a list and have the city combo box then present only relevant cities from that state? Many businesses have a predefined set of city-state combinations they support, and being able to select these rather than type them each time both speeds up data entry and improves data consistency. To be prepared for this lesson, you should already understand the following key topics. First, the basics of table relationships are essential, as are relational combo boxes that let you pick from a linked table. Also required is the knowledge of how to set criteria in queries based on values from a form. For today's task, you'll need to use one line of VBA code, specifically inserted into the After Update event for the combo box as well as the On Current event for the form. If you haven't written any VBA code before, don't worry. The code needed is short and straightforward, and I'll walk you through exactly where to place it. Let's start by looking at the setup. In a typical customer table, you might just have City and State text fields where users can type anything they want. However, to control these entries, we'll shift to using lookup tables for states and cities, and then connect those to the customer table. Begin by making a table for states, where each state has a unique ID, an abbreviation, and a name. I recommend including a separate ID field for most tables rather than using something like the abbreviation as your primary key. Populate your state table with only the states you need. For example, you could enter Florida, New York, California, Texas, and Hawaii. Next, create a city table. Each city record should have a unique ID, a state ID that relates it back to the state, and the city name. Enter cities for each state using the relevant StateID as a foreign key. For example, populate Florida with Miami, Fort Myers, Fort Lauderdale, and Cape Coral, while New York could have Buffalo, Rochester, and Syracuse. Once the lookup tables are ready, modify your customer table to store StateID and CityID integers instead of plain text names. If you're working with an existing database and do not want to lose current city and state data, you will need update queries to migrate values over to the new table structure. With the table structure ready, move to the customer form. Remove the old city and state text boxes and replace them with two combo boxes. The first will let users pick the state, and the second will present cities filtered to that state. It is best to select the state first, then the city, to ensure proper filtering. For the state combo box, set it to show states from your StateT table. Ensure the bound column is the state ID and that it stores its value in the StateID field of the customer table. Adjust the appearance as needed. For the city combo box, you need a query to provide a list of cities filtered by the currently selected state. The query should include CityID and CityName from your CityT table, with a criteria that reads the value of the state combo box on your open form. This will ensure only cities matching the chosen state will display. Set up the city combo box to use this query as its source, binding the stored value to CityID in your customer table. It's important to name your controls appropriately so that you can reference them easily in code. For example, you might call the state combo box StateCombo and the city combo box CityCombo. At this point, you'll notice a potential issue. When the state selection is changed, the list in the city combo box won't automatically refresh unless you close and reopen the form or switch to another record and back. To correct this, use the After Update event of the state combo box and the On Current event of the customer form to add a single line of code: requery the city combo box. This causes the list of cities to immediately refresh based on the current state selection, both when changing the state and when navigating between records. This approach greatly improves data consistency and makes record entry much easier. Users are forced to pick only from the available options, eliminating misspellings or invalid entries. This becomes especially valuable when you want to run reports or queries grouped by city or state. If you want to allow users or administrators to manage the list of possible states or cities, you can create simple forms for editing these lookup tables. A single form can show state details, and a subform can present a list of associated cities. With these forms, it's easy to add, edit, or remove options from your lists without directly working in the tables. To make managing these lists even easier, Access offers a feature called Value List Edit Form. This allows you to provide a button right within the combo box dropdown so users can jump straight to the editing form when needed. When configured properly on both state and city combo boxes, this feature lets users expand the available choices directly from where they're working. In the extended cut of today's lesson, I explain how to set up this kind of cascading combo system on a continuous form, which requires a slightly different approach and doesn't need a separate query for filtering. I demonstrate how to perform the filtering using SQL statements as row sources for the combo boxes, streamlining the process. Extended cut videos are available to all Silver members and above, while Gold members gain access to the downloadable sample databases used in these lessons. Higher membership tiers include access to even more advanced content and full-length courses, not just for Access but for a wide range of Microsoft Office and other technology topics. You'll continue to find frequent, free TechHelp videos here. If you appreciate these lessons, please support the channel by liking the videos, subscribing, and joining my mailing list for updates. For detailed, step-by-step instructions on everything covered here, you can find a full video tutorial on my website at the link below. Live long and prosper, my friends. Topic ListCreating state and city tables for cascading combo boxesSetting up primary keys and foreign keys in tables Populating state and city tables with sample data Modifying the customer table to use StateID and CityID Using update queries to convert existing text fields to IDs Designing combo boxes for state selection in a form Naming combo boxes for easier reference in VBA Creating a query to filter cities by selected state Configuring the city combo box to use the filtered query Hiding key columns in combo boxes for cleaner display Writing VBA to requery the city combo box after state changes Using the After Update event to trigger requery in VBA Using the On Current event to trigger requery in VBA Ensuring cascading combos update correctly when navigating records Demonstrating improved data entry control with cascading combo boxes Designing a form and subform for editing states and cities Binding forms to tables for easy data management Setting up parent-child relationships in subforms Using the List Items Edit Form property in combo boxes Enabling users to edit combo box lists via a popup form Syncing combo box lists after editing underlying data |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access cascading combo boxes, city state select, link combo boxes, dependent on another combobox, multiple cascading combo boxes, cascading combo boxes in continuous forms, choose from list of cities in state, List Items Edit Form PermaLink Cascading Combo Boxes in Microsoft Access |
