Multiple Cascading 3
By Richard Rost
12 months ago
Make Multiple Cascading Combo Boxes in Access Part 3
In this Microsoft Access tutorial, I will show you how to build multiple cascading combo boxes by setting up a customer table with essential fields like address, city, state, and ZIP codes. We focus on normalizing data to use IDs efficiently, modifying combo boxes step-by-step, including writing and revising VBA code to ensure the fields update correctly as selections are made. Additionally, you'll learn how to manage row sources dynamically to filter data based on previous selections, and ensure a smooth transition when browsing records. This is part 3.
Members
There is no extended cut, but here is the file download:
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!
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, cascading combo boxes, nested subforms, update query, customer table IDs, converting data systems, normalization in databases, combo box row source, filtering combo box data, after update event, VBA subroutine setup, Access event handling, control source modification, updating drop-down lists, debugging Access forms
Intro In this video, we continue with part three of the multiple cascading combo boxes series using Microsoft Access. I'll show you how to set up your customer table to store IDs, create and configure combo boxes for country and state, and filter them based on user selections. We'll cover cleaning up your SQL row sources, managing form events like After Update and On Current, and updating combo boxes with VBA subroutines. This lesson focuses on linking your form fields in the correct order and keeping your data organized as you build out your cascading combo boxes. This is part 3.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost. Today's part three of my multiple cascading combo boxes series. Go watch parts one and two if you have not yet already done so, and then come on back.
All right, so far we've got our table set up. We've got our form set up so we can edit that data, which is right here with our big set of nested subforms. Now the key is to get this data into our customer table. So in our customer table, we've got address, city, state, and zip, typical fields. But we're going to need to store IDs in here in order to use our new sets of data.
Now here's the tricky part. If you want to keep all of this data, you'll have to convert it over to the new system. I'm not going to cover that in this series because I've already covered it in a few other videos. You'll just use an update query and then run a series of update queries. So Fort Myers, for example, is field whatever in the city table, right? Whereas Fort Myers is city one.
So what you'll do is you'll add city ID in here, and then you'll run an update query. This is okay for every Fort Myers. Change, well, add city ID and then say for every Fort Myers update city ID to one. You can run it down the entire table here. You have to make sure all of these entries are in this table, and then they'll all be updated to city IDs, and then you can just go through and delete the city text field.
Again, I've got other videos covering that. Here's one of them. In fact, I go over this exact example. I'll put a link down below. In fact, I think what I'm going to do is as a follow-up to this series, I'm going to make another video, and I'm going to walk you through normalizing all of this data properly. Normalizing is basically what I just talked about, taking all of the Fort Myers out of here and putting them in the city table as Fort Myers and replacing these with a one. That's called normalization. You don't want duplicate data in your database if at all possible. We'll do a separate little video on that or maybe a mini-series on that.
For today, I'm not going to bother with all that. All right. We're just going to skip it. I'm going to just delete this stuff and put the proper fields that we need in here. Just so you're aware, obviously you've got data you want to save. You don't want to just wipe all your data out. But for today's class, that's what we're doing. All right.
So address, city, and state we're going to nuke and country. So I'm going to delete those, and we're going to delete country. I'm going to leave zip code. Zip code is outside of what we're working with today, and zip code has got a whole bunch of other stuff. Go watch this video if you want to learn about zip code lookups and all that kind of stuff. I'll put a link down below. We're not dealing with zip codes today.
So we are going to, whoops, we are going to have, what are we going to do here? We got country ID. I can't fight today. Number. We've got just an order. State ID number. County ID number. City ID number. Street ID number. And then the funny thing is those are all numbers, but street number is going to be short text.
You might get 114A Main Street, right? So you need to be able to put numbers and letters in the number field. I've had customers where they have a customer number, and the number has letters in it or part numbers with letters in it. So why do we name things the way we do? Who knows? It's called a street number or street location designation or whatever. I guess from a database standpoint, it's really more of an identifier, not a true number.
All right, 221B Baker Street is, you know, it's not a street number. It's a street identifier, I guess. I don't know. People will probably always say street number forever. I just looked it up online, and even the US Postal Service calls it a street number, like 114 Main Street. That's the primary address number, they call it. Main is the street name, street is the street suffix. If there's a unit, apartment, or suite number, that's called a secondary address unit designator.
So there you go. More useless information you didn't need today. Save it, and let's move on to the customer form now. Got a whole bunch of pound names on here because we deleted a whole bunch of information. Let's get rid of that stuff. Let's see here. We get rid of you guys. Temporarily, we're just going to move zip code over here. We'll deal with you later.
Let's start off by making a combo box for country. This is the easiest one because it's not dependent upon anything else. You're just gonna list all of the countries, right? So form design, grab a combo box. Where are you? Right there. Drop it there. Look up the values in the table or query where you get your stuff from. Country T. Both fields, please. I'm gonna sort by country name, and I've got a whole separate video, by the way, if you want to have certain popular countries at the top of the list. It's called USA first. I know I'm not trying to be overly patriotic. I just got the USA first because I'm in the USA, and most of my customers are so that I want that at the top of the combo box. Okay. Okay.
And yes, I am patriotic. I don't always agree with my government's decisions, but I love my country. Okay. Next, that's what it's going to look like. Next, store the value in the country ID. Next, and give it a label. Country is fine, and there we go. I'm gonna format paint to there. Slide you over a little bit, and there's the country. Let's make sure this box is given a good name. I'm going to call it country combo. All right, and I recommend you use the same naming conventions that I do. Later on, it will be important.
Now, let's take a peek at the row source for this combo box. I want to clean it up a little bit because Access puts a lot of information here you don't need. Access doesn't know that I'm using my naming conventions, so it puts brackets around everything. If it's based on one single table, you don't need the table designation in here. So I'm going to clean this up. So let's do this. So we got country ID, comma, Country name, comma, from country T, order by country name. That's all, and you don't really need that semicolon either. That's designed for other database systems like SQL Server where you can have multiple statements in a row. But for this, you don't need it. Put it there if you want. Okay. Nice and straight and simple. Hit okay.
All right, save your form. Always save often so you don't lose your work. Now let's make another combo box for the state combo. We're not going to worry about any filtering just yet. I just want to get the combo box there. Now, you can either run through the wizard again like we just did, or you can just copy and paste. Watch. Copy, paste. All right, once you learn how to do this manually, it's faster than the other way. Change this to state. Open her up. We're gonna call it state ID for the control source. Come here. Where are you? State ID for the control source. State combo for the name of it. Go to data, and in here we're just gonna change this from country to state. Watch, state ID. Copy. All right, everywhere you see country, just change it to state. Okay, good enough. Okay. All right, save it. Close it. Save it. Close it. Open it, and there's my states. All right, now if I pick myself a country like the United States, I want this to be filtered. Right, we're going to change the row source so we're only seeing the states from the country that was picked.
All right, so here's what we're gonna do. We're gonna go back into design view. I'm going to go back to that row source here. Okay, what I'm gonna do is I'm gonna cut that data out of there. We're not gonna put anything in the row source in the combo boxes in here themselves except the first one because that's never going to change. Okay, and in fact, I'm just gonna drop that in my notepad for now. Hang on to that because we're gonna use it in a bit. Okay.
Now, why do I not want all of these combo boxes to have data in them? Because when the form loads up, okay, you don't want all of these combo boxes loading all of that data because you're just gonna overwrite it in a minute anyways. Right, when the form loads in the on current event, which happens in the form loads and when we move from record to record, from customer to customer, we're gonna look at the country ID, we're gonna filter this box, we're gonna look at the state ID, we're gonna filter the next box, we're gonna look at that box, we're gonna filter the next box. Right, we don't want to waste time loading these boxes with all that data when the form loads the first time if we're just gonna overwrite it anyways. So you might as well leave that blank. Don't put any data in it. We're gonna fill it in just a second. So we're gonna do it. Well, we need to put that code in two places. So if I need to put the same code in two places, it means I'm gonna make it its own sub. Right, we're gonna have to trigger it when the user picks a country and when the user moves from record to record or opens the form the first time. So that's the on current event.
So let's start with the after update event for the country ID. Go to events, find after update. All right, there's my code window. It's nice and big. Linear, resize it. Okay, there we go. Okay. All right, so in here, in the country combo after update, I'm gonna say update state combo, and I'm gonna make my own subroutine called private sub update state combo, and in here, this is where I'm gonna set the row source for that state combo box. I'm gonna say state combo dot row source equals remember that stuff I had to copy to your clipboard a minute ago? Paste it in. Okay, there's your row source. Now, I like to break this down line by line. When I'm doing it in code, it's easier to read, so we're gonna do that. And that, like so, I can move that up like this. Okay, and then we're gonna do this, and then like so. Okay, there's our row source. Now, we gotta add something to it. We gotta filter the data with a where condition based on the value in the country combo box. So right here, after the from, it's gotta go before the order by we're gonna say where country ID equals whatever values in the country combo, and gotta put a space after it so you don't, so you don't, this is a common mistake a lot of people make. They forget this, this trailing space, and they'll have a, you know, two, and then it goes right into order by without a space there, and that's bad. That's why a lot of the times what I'll even do so that doesn't happen is I'll just put an extra space in front. You don't gotta worry about it, but it doesn't look as nice. So either way you want to do it, that's fine. I'm used to it now. I've been doing this for so long. I always remember that space there, and if it doesn't work, I know what happened, right? So then we need our line continuation, and there you go. Okay, ready? Save it, throwing a debug compile once in a while, and let's see what happens here now.
I'm gonna close this guy and reopen it. Now we only put it in the after update event. Okay, right now I got nothing in there. Let's pick Canada. Drop it down. Look at that. See, I updated this, and then it updated that. Pick United States, and there's the United States states. See, we need one more place. We need it when we move from record to record. So now we need to also put it in the on current event for the form, and we don't need duplicate code because we've got update state combo, boom. Same, and now it'll call it the same place. So now if I come in here and I go to this customer and he's got United States, I see New York. If I go to the next customer and he's got Canada, I can pick Ontario, and if I go back to this guy, it updates it, and I can see New York in there, and if I go back to that guy, you can see Ontario. See how it works?
All right, so that's the training part. Now we got the practice. Now we got to do the rest of them. All right, so come in here, design view. We're gonna copy, paste, paste, paste. Slide these into place right there and right there and right there. That's the hard part. Ha ha. Then we got county, we've got city, city for, and street. And then for each of these boxes, all we really need to do is change the control source to county ID and county name. Right country or combo, I'm sorry, combo, state combo, county combo, city, city ID, city combo, street ID, street combo. Save it.
All right, let's focus on the after update events first. So we got the state ID. It's got the, oh, we got the country ID has this after update event, which does this. All right, update state combo. Okay, now we need to make an update combo for each of the other ones. Okay, so for the state ID, after update, this guy's gonna need an update, county combo. Okay, and we'll just copy and paste this one, right? Copy, paste. This is gonna be update county combo, and we can replace everywhere you see state in here, replace it with county, right, county, county, county. Leave country alone for a minute. We'll change that in a second. Okay, this is gonna be the guy above county, which is state ID equals state combo. Okay, as you can see, we're gonna repeat this five times. Now, some of the more advanced amongst you might be saying, well, that's a lot of duplicated code too. Can't we just make, yes, you can. Yes, you can make a single unified function that does all of this in one shot. It's a lot more difficult. And yes, I will be covering it in the extended cut because you got to pass controls around and figure out names and it's possible and I have a solution. I would be covering it in the extended cut. But for now, yeah, we got some duplicated stuff in here. It's just, it's slight. It's the same stuff. It's just slightly modified for each use case.
All right, I'm gonna write the rest of these while I'm in here. So we got county. So let's under county under county is city. So I'm gonna replace county with city. So, city, copy. Yeah, you can do search and replace and stuff, but I think it's, I get in trouble with it sometimes. So county, county, county, county. And then this becomes county. I've done search and replaces before and accidentally changed stuff that I didn't mean to change. So I usually do this by hand myself. All right, so below city, we now have street. So we actually don't need to update the street combo. Right, we just got to update country, state, county, and city. Oh, so we do need to update the streets combo under city.It's just that street ID doesn't have anything else to update after it. All right, so this is the last one here. I know we did four of these.
Okay, all right, so there's street, street, street, and city. Watch your spelling. Save it, debug, compile after a while.
Now we got to link these into the boxes. That's all. Okay, so state ID is going to be update county combo. County ID is going to be update city combo. And city ID after update is going to be update street combo. And then street ID, I usually just like to throw the focus over on some of the, we'll put the street number field over here later.
All right, so after you're done with that, you're just done with that for now. All right, save it. Close it. Close it. Open it.
All right, let's pick Canada. Let's pick Ontario. Let's pick Niagara. Niagara Falls, and I don't think I have any streets in there. Let's go to United States, and you can see we're gonna have to come back later and blank the ones below it. But for now, we're just getting the cascading working. We still got some tweaking to do, folks. Don't worry.
United States, Florida, Lee, Cape Coral, Pine Island Road, same and our cascading is working. So we got some stuff to do still. We got some work to do. We got to blank if they changed one of these above it. They got to blank to stop below it. Okay, and what happens if the user blanks the country? It messes up our SQL statement, so we're gonna fix that.
And lots more. You guessed it in tomorrow's video. So tune in tomorrow, same bat time, same bat channel. Members, you'll be able to watch it right now because I'm a recording crazy fool today.
But that's gonna do it for today's folks. That's part three. Tune in tomorrow for part four. And that's your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part four.
TOPICS:
Multiple cascading combo boxes
Setting up the customer table
Storing IDs in the customer table
Running update queries for data conversion
Deleting unused fields from customer table
Creating a combo box for country
Cleaning up row source SQL statements
Setting up a combo box for state
Filtering state combo based on country combo
Using 'After Update' events
Implementing 'On Current' event
Updating combo boxes through custom subroutines
Setting control source and combo box naming
Order of cascading combo boxes: country, state, county, city, street
Implementation of line continuation in VBA code
Managing duplicate data entries
COMMERCIAL:
In today's video, we're continuing with part three of our multiple cascading combo boxes series. We'll review how to get our table and form set up with nested subforms, and then we'll talk about storing IDs in your customer table for seamless data integration. You'll learn how to update your combo boxes to ensure they filter based on user selection, streamline row sources, and manage form events efficiently. Plus, we'll cover maintaining data integrity when fields change and discuss plans for normalizing your database in future videos. 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 objective of part three of the multiple cascading combo boxes series?
A. To introduce the concept of a relational database
B. To finalize the setup of cascading combo boxes
C. To set up the tables and forms for data entry
D. To teach how to convert data to the new system
Q2. What needs to be added to the customer table to store the new sets of data?
A. State names
B. City names
C. IDs such as City ID and State ID
D. Street addresses
Q3. What is normalization in the context of databases according to the video?
A. Adding new tables for every field in a database
B. Removing duplicate data and replacing it with references
C. Storing all data in one large table
D. Using only numeric data types for IDs
Q4. Why does the instructor recommend leaving certain fields blank when the form initially loads?
A. To ensure faster form load times and to update them dynamically
B. To minimize database storage usage
C. To enforce user input validation later
D. To avoid any potential SQL errors
Q5. Which event is used to update the state combo box when a country is selected?
A. On Click Event
B. After Update Event
C. On Open Event
D. Before Insert Event
Q6. What does the term "cascading combo boxes" mean in this tutorial?
A. A feature to display a dropdown list of sports teams
B. Combo boxes that automatically populate based on another combo box's selection
C. A series of connected checkboxes
D. A method to link text boxes to combo boxes
Q7. For what reason is the 'street number' field set as short text even though it might contain numbers?
A. Because of user preference
B. For correctly representing mixed data like '221B Baker Street'
C. To match it with other ID fields
D. To simplify form design
Q8. How does the instructor suggest handling the row source for combo boxes when a form opens?
A. Load all data indiscriminately for faster processing
B. Set the row source to a temporary dataset
C. Leave it blank initially to avoid redundant data loading
D. Use a default SQL query for each combo box
Q9. What is suggested as the source to populate the first combo box for country selection?
A. External API data from global sources
B. A hardcoded list of popular countries
C. Values from a predefined table or query called Country T
D. User input directly
Q10. What is the purpose of the 'on current' event in this context?
A. To validate data entry by users
B. To ensure a consistent focus on user interaction
C. To update the combo boxes when the form loads and when moving between records
D. To reset form fields when errors occur
Answers: 1-B; 2-C; 3-B; 4-A; 5-B; 6-B; 7-B; 8-C; 9-C; 10-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 Access Learning Zone focuses on the third part of our series about creating multiple cascading combo boxes. If you've missed parts one and two, I suggest watching those before continuing with this tutorial.
We've already set up our tables and forms for editing data with nested subforms. The next step is to integrate this data into our customer table. Our customer table contains standard fields such as address, city, state, and zip code. However, to utilize the new data structure, we'll store IDs corresponding to these fields.
If you want to maintain your current data, you'll need to transition it to the new system. I won't cover that process here since it has been addressed in other videos. The method involves using update queries to match existing city names to their corresponding IDs. For example, for each "Fort Myers," we match it to the correct city ID. You'll need to ensure all entries are updated and then remove redundant text fields.
I plan to create a follow-up video that showcases how to properly normalize data. This involves moving city names like "Fort Myers" into a dedicated city table and replacing existing entries with their respective IDs to avoid duplicated information in the database. For today, we'll focus on updating the customer table's structure for our example, which means removing the address, city, and state fields while keeping the zip code intact.
We'll add fields for country ID, state ID, county ID, city ID, and street ID in the table. It's important to note that, while these are all numerical fields, the street number remains as short text because it may contain alphanumeric characters. This is similar to scenarios where customer numbers or part numbers include letters.
As we proceed with designing the customer form, we'll start by creating a combo box for countries since it's independent of other fields. Using a combo box, we'll list countries from the appropriate table. You can customize it to display frequently used countries at the top, like the USA for those predominantly dealing with customers from the United States.
After setting up the country combo box, I'll adjust it using my naming conventions to make it consistent and easier to manage. We'll move on to creating a combo box for states, setting up similar controls, and ensuring the combo box only displays states from the country previously selected.
We have to efficiently manage data by using the 'on current' event of the form and the 'after update' event for each combo box to set the row sources accordingly. This ensures that each selection filters the next combo box in the sequence. The goal is to prevent each combo box from loading unnecessary data until it's required, streamlining the process.
For the state combo box, we'll adapt the same method, adding conditional logic to filter states based on the selected country. This practice continues for county, city, and street fields. Although you might notice some repetitive aspects in the code, it ensures each step is tailored to its respective function.
Once everything is linked and working, we'll ensure that changes in one combo box properly reset dependent combo boxes to maintain consistency. If any issues arise, such as resetting the SQL statement, we'll address those in upcoming tutorials.
Join me tomorrow for part four, where we continue refining this project. You can access a comprehensive video with step-by-step instructions on everything covered here on my website at the link below. Live long and prosper, my friends.Topic List Multiple cascading combo boxes
Setting up the customer table
Storing IDs in the customer table
Running update queries for data conversion
Deleting unused fields from customer table
Creating a combo box for country
Cleaning up row source SQL statements
Setting up a combo box for state
Filtering state combo based on country combo
Using 'After Update' events
Implementing 'On Current' event
Updating combo boxes through custom subroutines
Setting control source and combo box naming
Order of cascading combo boxes: country, state, county, city, street
Implementation of line continuation in VBA code
Managing duplicate data entries |
