Welcome to another TechHelp video brought to you by accesslearningzone.com. I'm your instructor Richard Rost. Today is part two of my multiple cascading combo boxes series. So if you haven't watched part one, go watch part one first and then come on back.

In yesterday's class, we set up our tables: country, state, county, city, and street. Now we're going to make the forms that we can use to easily manipulate all this data. We're going to use nested subforms, which I have a separate video about. I put that in the prerequisites for the last video, but watch that if you haven't seen it yet.

Let's start. I'm going to copy a form now. In my TechHelp free template, I have a couple of template forms: the continuous and the single. These are going to be continuous forms. So I'm going to copy the continuous form. Copy, paste. We're going to call this one country form.

And then we're going to edit this. Now, we really don't need to see IDs in any of these fields or forms, so just get rid of them. Take this and slide it over here. This will be the country. Let's bind this form. Go to the "All" tab, go to "Record Source," drop this down, and pick "country." Make sure you get "country," not "county." I've done that a couple of times.

Then this can now be bound. The control source is going to be "country name." Copy it and make sure that you give the name of the control the same thing. Leave it wide because we got a bunch of stuff going underneath it. Save it, close it, open it back up again, and there we go; there are our countries.

Now we're going to close that and copy it. Copy, paste. We're going to call this the "state form." Design view. The first thing I'm going to do is change the colors. I want them all to look different visually. So I'm going to go into "Format" and go with blue for this one. This will be the blue form. Make it a little lighter blue. More colors. A little lighter blue. There we go. And this will be the state.

Open up the form properties. Change this to "state." That means this is no longer valid. That's okay. Change this to the "state name." Change this to also "state name."

You're probably asking how are we going to link this to the country table above it without any IDs. That's where these subform properties come in. Save this, close it, open up the country form again, and go to design view.

In the footer down here, make this bigger. Grab the state form and drop it right in there. You're going to get this message saying a form of the subform object can't have its default view property set to continuous forms. Access lies. It's a liar. I talked about this in the video where I teach the nested subforms. This is wrong. Access team, fix this. Hit okay.

Now it's going to switch the form properties back to single form. All you have to do is switch it back to continuous forms, and you're good to go. I hate that there's also a label for that subform, so we're going to get rid of that. We're going to slide this up right into the corner, not all the way, leave a little bit of purple around it. And then make this nice and big because we got more stuff coming in underneath it.

Save it, close it, open up the country form, and look at it. United States, you're just seeing Florida, New York, California. Canada, you're seeing Ontario, Quebec. We don't have any for the other ones, but see how that works. If you want to add more, you can. We're going to put Vermont in there.

Close it, come back in, and there it is. That happens because, in the subforms properties, there are "link master fields" and "link child fields." That country ID is the same between both of these. That's why I said at the top of the last class, it's important that you stick to my naming conventions. That's got to be named exactly the same thing in both of those tables, or it won't find that relationship. You can make the relationship yourself by typing it in if you used different field names, but I don't recommend it.

Let's move on to the next one. What do we have next? We've got our state form done. Let's make county next. I'm going to take state. Copy, paste. This will be the county form. County. I'm going to pronounce it like that, so I remember from now on.

First thing again, I'm going to make the colors green for this form. Drop it down. I always stick to the bottom color choices. I talk about this in my beginner class because theme colors can change. The users can sometimes change their theme, and then you're stuck with something you don't like. That's too bright of a green. Let's go a little darker. Maybe down there. That's good. Click down here, change that to that. Change it here, and then we'll lighten it up a little bit but not too much, and maybe something like that. There we go.

Yes, making your forms pretty is important. If you have a nice user interface that's easy for your users to understand, it improves workflow. People have sent me some databases and screenshots, and I'm like, what is going on? You want to ensure you've got a nice, pretty interface for your people to work with.

Form properties. What are we getting this from? County. Change this caption. County. Change this to county name. Copy, paste. Save it, close it, open up the state form. I'm only going to work one step up each time. Just the state form. Do the same thing. Make this bigger down here. Drop the county form in there. Click okay. Go back here first thing and change it to continuous forms. Get rid of the label. Slide this up a little to make it bigger. You may have to come back and play with the sizing later to make it look exactly the way you want because you might want more cities displayed than countries.

Save it, close it, go back up to country, and there are all three of them. You'll have to play with the sizing a bit. There's Florida, there's New York.

Let's make the state one a little bigger inside country design. You'll have to enlarge them. I have limited space on the screen for the recording window. Save it, close it, open it. That's a little better. Okay.

As a side note, you could go side by side with these and have synchronized subforms next to each other. There's a trick to it. I covered this in a video where you can have one parent form over here and the subform over next, like two subforms. It's more complicated and I won't cover it in today's video. If you want to learn how to do that, go watch this linked video below. For today we're just going to stick with the nested subforms. And yes, we're going to do them all. This is the practice part of learning.

In fact, I have a separate video planned on the difference between training and practice. Training is learning something new. Practice is practicing it to become better. I've already taught you most of these techniques. We're going to learn some new stuff in upcoming videos, but everything we've done so far is practice. It's stuff I've covered in other videos. We're putting it together. We're putting the Legos together in different ways and practicing our skills.

In some of my complete courses, I give homework where it's appropriate. We go over the homework, practice it, and later I'll show my solution. One of the best ways to learn is by doing it. That's why it's better if you build this database with me instead of just downloading my copy.

Now it's time for city. Find your county form, copy, paste. We've got city form. Right click, design view again, we're practicing. We can change the color first. Let's go with yellowish. Format, dropdown, pick yellow, not that yellow. Let's go to colors, make it dark yellow, like dark tan. Fancy. Then in here, we'll go a little lighter, like that. That's good.

This is going to be city. We're going to change this to city. Change this to city name. Copy, city name. Save it, close it. Once you get it figured out, you're on a roll doing it. It's faster. City goes inside a county. Open county, make it bigger. Drag city in there. Change this thing to continuous forms. Remove the label. Slide the form to here. We're running out of room, but that's okay.

Save it, close it, go back to the top. Country right there. Close this up. Oh, I just maximized it accidentally. There we go. Let's see, can we see everything? We can't see any cities. Let's make city bigger inside of county, so open county. Design view, extend it. Not by much, just a little bit, like that. I told you this is trial and error to get it right. That's not too big. Let's see what happens next.

Now we have to stick street inside of city. Ensure these things work. Close that up, time for street inside of city. Take city, copy, paste. We're going to do street form. One more time with practice here, design view. Let's make the color red. We don't have a red one yet. I'm going to pick that shade. I usually pop this thing open when designing and close it when looking at stuff. Maybe there, yeah.

This will be street. Double click here. Change this to street. Click on this, street name. Copy, paste, save it, close it, open city design view. Drop you in there. Do the thing, do the other thing. You right that. Okay, we should be able to see at least two or three streets. Save it, close it, let's go back to the top.

All right, country closes up. Sometimes you get little screen aberrations with five nested subforms. I think it gets a little weird sometimes. Later in the extended cut, I'll show a better way of doing this instead of just using these nested subforms. If you're a member, don't worry; we have something better coming up. This does work, though. I've built many databases like this.

At this point, you might be asking about relationships within these tables. Why don't I set up relationships in database tools? I don't usually use these, to be honest. You can if you want to; they have their purpose. I have a separate video on relationships and referential integrity and cascade deletes. I'll put links to those videos below. You can set up a cascade delete where, if I delete California, it'll delete all data underneath it. That's a cascade delete. I'm very careful with those.I don't use those often, usually only for temp data. But there's certainly a possibility you can if you want to. Again, I usually don't. But if you want to learn more about that stuff, I'll put links down below.

All right, so now we've got all of our tables set up, and our data is in the tables. We've got a big form set up so we can modify, manipulate, and edit that data. The next step is moving into our customer form and actually setting the fields up in here and getting the cascades going.

All right, it's gonna look like this: pick your country, pick your state, see the next one drop down for you automatically, pick your county, pick your city, pick your street, and then it puts a focus over here to type in your number. And there you go; that's what it's gonna be like when we're done.

We're getting there. We're getting close. So tune in tomorrow at the same bat time, same bat channel. And of course, members, you can watch it right now because I'm gonna keep recording today until I'm done.

Yep, that's gonna do it for part two folks. That's your TechHelp video for today. Hope you learned something. Live long and proceed with my friends. I'll see you tomorrow for part three.

TOPICS:
Setting up tables for cascading combo boxes  
Creating a country form  
Binding forms to data sources  
Linking forms without IDs using subform properties  
Adding subforms to a main form  
Styling forms with different colors for visual identification  
Adding state, county, city, and street forms  
Arranging nested subforms for displaying data  
Troubleshooting form display with nested subforms  
Explaining link master fields and link child fields  
Understanding cascading deletes within tables  
Modifying data using a hierarchical form setup

COMMERCIAL:
In today's video, we continue with part two of our multiple cascading combo boxes series. We're creating forms to manage your country, state, county, city, and street data. You'll learn how to copy and customize continuous forms, bind them to specific data sources, and set subform properties the right way, despite Access's misleading messages. Plus, we'll make those forms visually appealing with some color changes. We'll also talk briefly about relationships and why they're not always necessary for this setup. Finally, you'll see how everything ties together to build a cascading selection of locations. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.