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.