Multiple Cascading
By Richard Rost
12 months ago
Make Multiple Cascading Combo Boxes in Access In this Microsoft Access tutorial, I will show you how to create and manage multiple cascading combo boxes, allowing you to link up to five levels of data hierarchy such as country, state, county, city, and street. We will explore advanced techniques using queries, SQL, and VBA to build these cascading combos effectively. Starting from setting up necessary tables for our dataset, I'll guide you through each step to ensure your data is efficiently managed and organized. This developer-level tutorial builds upon previous lessons and provides a foundational approach tailored for more complex cascading requirements. MembersThere 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!
PrerequisitesRecommended CoursesUp Next
Keywords TechHelp Access, cascading combo boxes, multiple cascading combo boxes, five-level cascading, combo box cascade example, cascading combo box setup, VBA for cascading combo boxes, SQL cascading combo boxes, nested subforms, data relationships tutorial, Access query for cascading, Access VBA if then statements, Access On Current event, Access After Update event, Access table setup, Access country state city example, Access street data mapping, Access table relationships, Access customer form tutorial, Access TechHelp template, Access sample data entry, Access developer-level tutorial
Intro In this video, I will show you how to create multiple cascading combo boxes in Microsoft Access, building a five-level setup with country, state, county, city, and street selections. We will walk through setting up the necessary tables and entering sample data, discuss how to use queries, SQL, and VBA code for cascading updates, and cover the importance of table relationships. This tutorial is perfect if you want to expand beyond the typical two-box example and see step-by-step how to organize your data for advanced cascading combos.Transcript In today's video, we're going to learn how to do multiple cascading combo boxes, up to five of them. You can do as many as you want with the same technique, but I'm going to show you up to five.
Now, one of my more popular videos is my cascading combo boxes video. That's where I show you two boxes. In the first box, you pick the state, and then it will cascade to the second box so it will only show you cities from that state. So it's basically one box that gets its values from another one.
Well, of course, one of the most popular questions I get is, how do I do more? How do I do country, state, city, or country, city, state, street, and all that stuff? Today we're going to do a five-level example, five tiers: country, state, county, city, and street. Now I'm leaving off some things like zip code because you can just type in the zip code. I've got other videos on how you can do zip code lookups and stuff like that. Street number will be a different field.
There are lots of different reasons why you might want to do something like this, if you just care about demographic regions or whatever. But the actual fields are not the point. The point is I'm going to show you how to do five cascading combo boxes in a row. So if you've got product category looking up products, looking up descriptions, whatever.
Now, the first example we just used queries in the previous video. So that was basically an expert level video. In fact, this video is before I was even doing the beginner expert developer thing. But we needed one little bit of VBA code just to force the cascade to happen. But all the data came from a query.
So today, we're taking it up a notch. This will be a developer level video, so we're going to use queries. We're going to use just straight SQL and VBA. So it's a little more advanced.
Let's take a look at our prerequisites for today's class. First up, of course, if you haven't watched my previous cascading combo boxes video, go watch that first. Also, watch my video on nested subforms. This is how we're going to initially set up the data: countries, states, counties, and so on. We're going to do five levels of nested subforms. It's going to look like this:
If you're not too familiar with VBA, watch my intro to VBA video first. It's about 20 minutes long and will teach you everything you need to know to get started. If you need to brush up on your SQL with Access, watch this. We're not doing anything too complicated, but we are going to make a basic SQL statement to select from where and order by.
Make sure you understand if then statements, the on current event, the after update event, how to get a value from an open form, the on open event, and there's probably going to be some more. These are just some to start with. This is going to probably be a couple of videos, so I'll point you to more lessons if you need them as we're going along.
For sure, this goes without saying you should definitely have a good handle on relationships. One to many specifically for these examples, but obviously relating values between different tables is going to be crucial. These are all free videos. They're on my website and my YouTube channel. If any of that stuff seemed Greek to you, unless you're from Greece, of course, then go watch those videos so you understand that stuff before continuing with this series.
Here I am in the TechHelp free template. This is a free database. You can grab a copy from my website if you want. But you can do this stuff in any database. I don't think anything I'm doing is specific to this, except we are going to use my customer form and put those fields in here that we talked about before we get to the customer form. Let's set up all of our data.
Now I'm going to set up the tables that we need to do all of our cascades. We're going to set them up together so no one complains about how to do that. Sometimes when I skip steps, people complain. I would rather have people not be lost if I skip steps, assuming that you know something, rather than other people complain about why you're taking so much time on this. If you don't like it, you can fast forward. I don't want to lose anybody. My goal is to teach. You can play it on 2x speed if you want to. But we're going to create a bunch of supporting tables first. Country, state, county, city, and street are going to be tables, each one relying on another.
We're going to create the country table first: Country ID. I strongly recommend you stick with my naming conventions because some of the code we're going to use later depends on this. They're good naming conventions. They're my naming conventions. Country ID and then country name, not just name, never use just name with the reserved word. That's all we need in this one. Save it as CountryT. Primary key, yes.
Let's put in some data. I'll just put in a few sample records. We've got the United States, Canada, the United Kingdom, France, Germany, and Mexico. Put in as many as you want. Keep an eye on what these IDs are because we're going to need that for the next step.
Save that. Now it's time for the state table. I know it's not called state in other countries. Canada calls them provinces, for example, but just bear with me. If you want to name it something different, feel free. You can do little tricks later on where you can have a list for each country of what they call their states or their geographical divisions. I've built databases before for clients of mine that had customers in both the US and Canada, and if it was a Canadian customer, it knew to flip it to province. Those are all embellishments you can add later. It's all the basic stuff.
Create table design. This one we've got states. State ID. That's our outer number. We need to know what country the state is from. Country ID. That's a number of type long integer. That's our foreign key. Then state name. Save that as the StateT. Let's put some sample data in.
Now we've got to know what the countries are for each of our states. We're going to make forms to do this later, but for now, we're going to type in the IDs ourselves. United States: Florida, New York, California. Then we'll do some Canadian: Ontario, Quebec, or Quebec, I believe you guys pronounce it, and Alberta. Correct me if I'm wrong.
Save it. We can close the CountryT. Move this guy up here. Now we can make the next table, which is our county table. Yes, I know in Canada you guys don't always call them counties. You've got some different rules up there. But, same kind of thing.
Again, create table design. We've got our county ID. Don't confuse county with country. I did this a few times when I was building my prototype. State ID. That's our number. That's our foreign key. County name. That's going to be short text. Save it as CountyT.
Some sample data. Again, just a few records down. This is the State ID over here. We'll go with Florida. I live in Lee County. There's also Collier County to my south and Charlotte County to my north. Let's go up to New York. I used to live in Erie County there. There's also Genesee County and Niagara County. Everyone always spells that wrong.
Let's do some from Canada. From Ontario, which is State ID 4, skipping California. We also have a Niagara County or whatever you guys call it. We have a Toronto. Toronto, from what I read, is both a county and a city. It's like the city is its own county, which is kind of cool. What do you guys call it? Geopolitical geographic division or something like that? Then Hamilton, same deal. Hamilton used to have its own county. I was reading about it. It was fascinating. You used to have a county name, and then they merged it with something else. Now it's just Hamilton and blah blah blah.
This is Access learning Access and Rick's weird trivia too. We've got counties done. We can close the State table, slide this guy up. Now we'll make our city table.
Create table design. We've got City ID, auto number. Again, repetition is good here, folks. County ID that the city's in. That's our number, our foreign key. City name. Save this as the CityT. Let's put some cities in.
Let's see here. Again, I'll stick with Lee County. We've got Fort Myers, Cape Coral, which is where I live now, Benita. This is one of those interesting situations where Benita Springs, most of it is in Lee County, but some of it is in Collier County. It's where a city spans multiple counties. That happens. I would put one in each of those.
Of course, we got Naples, Beautiful Marco Island. Let's go up north. We've got in Erie County, Buffalo, Hamburg where I was born and raised. I was born in Buffalo, raised in Hamburg. Then let's go to Amherst where I lived most of my life. Let's go up to Toronto. There's Toronto the city. We've got Hamilton the city. We've got Niagara Falls, the city inside of Niagara County. We need another one here: one in New York, one in Ontario.
I just looked it up. Niagara Falls, Ontario, is called the Regional Municipality of Niagara, which is essentially the Niagara Region, Ontario, Canada. Isn't government and divisions like this interesting? I think so. It's like Sheldon Cooper's fun with flags. I used to love flags. In fact, they used to play trivia every week at this place. Usually, at the end of the night, they had a world flag, and you had to guess it, and you could double your points. So I built a database to study world flags. I had them all down. I knew most of them for a while but forgot them all. One of the reasons I got flag badges on my website. I love flags.
We've got our tables all set. If it changes, yes. We've got our country, state, county, city. Oh, yeah, street. We've got to set up our streets. How can I forget the streets? Let's bring cities back. I thought we were done. When I get gabbin with you guys, I forget stuff.
One more table. Create table design. We've got Street ID. City ID. That's our foreign key. I know I'm repeating it, but you'll learn. Save it. We've got StreetT.
When it comes to streets, if you're doing something like this, you can put street name in its own field. If you're getting this, exact what the address is. Then we'll have a street number as a separate field. I personally like to put whether it's a street, boulevard, avenue, drive, parkway, all that, I put that as part of the street name. Because here, for example, in Cape Coral, you might have Northeast First Street. You also have a Northeast First Lane, Northeast First Drive, and so on. Those are all individual streets. At least that's how I figured it out.
For example, here we've got in Fort Myers, McGregor Boulevard, Colonial Boulevard, lots of boulevards, Metro Parkway. You can spell those out if you want to. Cape Coral has Pine Island Road, Santa Barbara Boulevard, Cape Coral Parkway. Then we got those weirdly named streets, which are most of them. The interesting thing in Cape Coral is that we have more canals than any city, I think in the world, more miles of canals, including even Venice, Italy. But our roads will be divided by a canal. Like you'll be driving down First Terrace, let's say, and then it'll stop, and there's a canal there. Then First Terrace will continue on the other side of the canal. But you've got to go all the way around. Like here, it was Northwest 13th Terrace, and it continues over here. Whoever designed this, I got no idea. But this is all over the city like this. I don't know what they did before GPS.
You've got stuff like Northwest 13th Terrace, Northwest 13th Road, Northwest 13th Street, and so on. It's just nuts. Move that over here.
Then we got, let's go up north again. City 9, which is Amherst where I used to live. We've got Sheridan Drive, Niagara Falls Boulevard in Amherst that goes to Niagara Falls, and then we've got Millersport Highway.
Good enough. Now we've got all of our tables set up. We've got some sample data in them. We're ready to make our forms so that we can do data entry nice and easily without having to rely on these tables.
We'll do that in tomorrow's video. So tune in tomorrow, same bat time, same bat channel. Or if you're a member, you can watch it right now because I'm going to record a whole bunch of these today. I'm leaving tomorrow for the MVP Summit up in Redmond, Washington. I'm going to be gone all week, so I'm preparing the series while I'm gone for you to enjoy. If you're a member, you can enjoy it all right now. If not, we'll see you tomorrow.
That is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Creating multiple cascading combo boxes
Five-level cascading combo box setup
Access data tables for cascading combo boxes
Country table setup and data entry
State table setup and data entry
County table setup and data entry
City table setup and data entry
Street table setup and data entry
Form creation for cascading combo boxes
SQL basics for cascading combos
Using VBA to force cascading updates
Understanding relationships for combo boxes
Creating supporting tables for cascading combos
COMMERCIAL:
In today's video, we're learning about creating multiple cascading combo boxes in Microsoft Access, expanding to five levels: country, state, county, city, and street. Many have asked how to go beyond the basic two-level setup. We're stepping up the game with advanced techniques using SQL and VBA. We'll look at setting up necessary tables and relationships, ensuring you're equipped to handle demographics or product categories, whatever your need. If you're unfamiliar with the essentials like VBA or SQL, check out our prerequisite videos first. We'll cover setting up tables with sample data so you're ready to build user-friendly forms for easy data entry. Catch the full series on my YouTube channel or website. Live long and prosper, my friends!Quiz Q1. What is the primary topic covered in today's video?
A. Understanding VBA coding basics
B. Setting up five cascading combo boxes
C. Learning SQL for database management
D. Introduction to designing user interfaces
Q2. What set of fields will be used to create the five-level cascading combo boxes example in the video?
A. Product, Category, Description, Price, Inventory
B. Email, Phone, Address, ZIP Code, Country
C. Country, State, County, City, Street
D. Region, Zone, Area, Section, Block
Q3. What is required to make the cascading combo boxes function as discussed in the video?
A. Expert knowledge of machine learning
B. Only using nested subforms
C. Using queries, SQL, and VBA
D. HTML and CSS styling
Q4. What is recommended before attempting to follow the video tutorial?
A. Understanding relationships and foreign keys
B. Completing an advanced JavaScript course
C. Prior knowledge of Python programming
D. Watching tutorials on design thinking
Q5. Which naming convention is suggested to avoid conflicts with reserved words in databases?
A. Using generic terms like 'Name' and 'Address'
B. Using specific prefixes like 'CountryID' and 'StateName'
C. Making them as short as possible
D. Using uppercase letters for all terms
Q6. What is the purpose of creating supporting tables like CountryT, StateT, etc., in the context of this tutorial?
A. To store all user interface elements
B. To eliminate the need for VBA code
C. To organize data that allows mapping and cascading
D. To aggregate financial data
Q7. Why is it important to understand terms like "on current event" and "after update event" in this context?
A. They facilitate real-time analytics
B. They are used to trigger actions in response to changes
C. They improve database security
D. They enable automatic data entry
Q8. Why does the instructor recommend using separate fields for street name and street type (e.g., Avenue, Boulevard)?
A. To make data entry quicker
B. Because databases cannot handle long strings
C. For clearer organization and differentiation of street types
D. To comply with international database regulations
Q9. What kind of problems might the cascading combo boxes solve as explained in the video?
A. Solving advanced mathematical calculations
B. Organizing demographic data hierarchically
C. Designing complex UX interfaces
D. Encoding video files for streaming
Answers: 1-B; 2-C; 3-C; 4-A; 5-B; 6-C; 7-B; 8-C; 9-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.Summary Today's TechHelp tutorial from Access Learning Zone will guide you through creating multiple cascading combo boxes, going up to five levels. While you can extend this technique to more than five, I'll demonstrate with five levels today.
Previously, I presented a popular video on cascading combo boxes where you select a state in one box, and the second box lists only cities from that state. The technique essentially involves one box deriving its values from another. A frequent query I receive is how to extend this to more levels, such as country, state, city, and other subdivisions. Today, I'll show you a five-tier example with country, state, county, city, and street, deliberately excluding fields like zip code, which can be typed in manually. I have other tutorials covering zip code lookups.
There are many reasons for implementing cascading combo boxes, such as analyzing demographic regions. However, the key takeaway is learning to create a series of dependent combo boxes, whether it's for product categories, products, or descriptions.
In my previous video, we utilized queries to accomplish this, classifying it as an expert level video before I even categorized tutorials by skill. We used a small amount of VBA code to ensure the cascade occurred, but all data was obtained from queries.
Today's lesson will elevate the complexity to a developer level, incorporating queries as well as SQL and VBA, making it slightly more advanced.
To prepare for this class, it's crucial to first view my previous cascading combo boxes video. Additionally, my video on nested subforms is fundamental, as we'll be implementing five levels of nested subforms for countries, states, counties, etc. These subforms are the basis for our setup.
If you're new to VBA, I recommend watching my introductory VBA video to gain the necessary foundation. For a primer on SQL with Access, I'll be using basic SQL statements - select from where, and order by - so familiarity with SQL is beneficial.
You should also understand if-then statements, the on current and after update events, as well as retrieving a value from an open form and the on open event. We'll likely cover even more, so I'll provide further resources when needed.
A firm grasp of table relationships, particularly one-to-many, is essential, as these examples heavily depend on linking different tables. If any of the terms I've used feel unfamiliar, check out my free videos on my website or YouTube to front-load your learning before proceeding.
I'm using the TechHelp free template today, which you can download from my site, but this methodology can be applied to any database. I'll utilize a customer form for our fields after setting up the data.
Now, let's create the necessary tables for our cascading setup without skipping steps. We'll establish supporting tables, with each one depending on the input of another: country, state, county, city, and street.
Beginning with the country table, I'll demonstrate a reliable naming convention that is key to our upcoming code. Once we input a few sample countries and note their IDs for next steps, we'll proceed with the state table. While names differ across countries - provinces in Canada, for instance - any necessary adjustments can be made later to fit specific needs.
We'll continue with the county and city tables, ensuring we align our sample data with the ID numbers correspondingly. During the setup, I'll draw attention to interesting geographical naming and administrative conventions where relevant. The aim is not only to populate tables but to emphasize understanding the relational underpinnings.
After completing the street table, integrating all required fields, we're ready to move on to the form creation phase for seamless data entry, removing reliance on tables alone.
Tomorrow's video will build upon today's groundwork by setting up the forms, allowing for intuitive data entry. For TechHelp members, these videos are accessible now, as preparation for the MVP Summit is underway, ensuring a steady flow of content even while I'm away. Non-members can catch tomorrow's installment at the planned time.
That's it for today's TechHelp tutorial. If you've gained new insights, great! Remember, you can find a comprehensive video tutorial with detailed steps on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Creating multiple cascading combo boxes
Five-level cascading combo box setup
Access data tables for cascading combo boxes
Country table setup and data entry
State table setup and data entry
County table setup and data entry
City table setup and data entry
Street table setup and data entry
Form creation for cascading combo boxes
SQL basics for cascading combos
Using VBA to force cascading updates
Understanding relationships for combo boxes
Creating supporting tables for cascading combos |
