Multiple Cascading 4
By Richard Rost
9 months ago
Make Multiple Cascading Combo Boxes in Access Part 4 In this tutorial, I will show you how to enhance the functionality of multiple cascading combo boxes in Microsoft Access, focusing on clearing options and managing related entries efficiently. We'll address how to automatically blank lower-level combo box selections when a higher-level selection changes, and update the cascading combos systematically using organized VBA code. Additionally, we'll add a user experience improvement by having the next combo box automatically drop down after a selection is made. This is part 4. MembersIn the extended cut, we will learn how to condense all of the update subroutines into a single subroutine called UpdateCombo. I will show you how to streamline the process by sending the combos and their parent to the new UpdateCombo subroutine, making the code more efficient and easier to manage. Silver members and up get access to all of my extended cut videos, so make sure to check it out if you're interested in learning how to optimize your combo box updates. 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!
PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp Access, cascading combo boxes, multiple cascading combo boxes, VBA code design, SQL statement error, update combo box states, IsNull handling, combo box null value, VBA form update, debugging VBA code, VBA set focus, VBA dropdown, user experience enhancements, after update event, access developer level 2, form state management, access form controls, cascading combo box logic, Canada and US combo options, advanced VBA logic
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost. Today's part four of multiple cascading combo boxes. If you haven't watched parts one through three yet, go watch those first and then come on back. Here we go.
All right, so yesterday we got our cascading combo boxes to work. You can pick United States, you can pick California, or we don't have anything on California to do it. New York, and then Erie, and then Amherst, and then Sheridan. I used to live right by Sheridan Drive in Millersport.
Okay, so we've got a couple of things to tackle today. First of all, if someone changes this to, let's go to Canada, we want all of these down below to blank because they're no longer valid options. And secondly, if they blank this, if also, say we don't, this is wrong, we get an error message because our SQL statement now says country ID equals null. So we've got to fix both of those things.
Okay, let's go back into our code design view. I've got a little button up here on my quick launch toolbar that takes me to the code for that form. It's really handy. I use it all the time.
All right, the first thing I want to do is I'm going to organize these four different things we have here based on the order that they run in. So we're gonna put country, okay, there isn't one for country. There's one for state, county, city, and street. Okay, they are in order. Good. All right.
When I was building the first time, I didn't have a lot of order. All right, so update state combo. We're gonna say right here, if IsNull the country combo, then state combo equals null, blank it, right? And then county or city, county, state combo.RowSource equals nothing and then exit sub. That'll blank that sucker right out.
So let's try it, save it, come back out here. All right, go into here. Let's put in United States and then New York and, okay, let's say, forget this, let's just blank this, and then look at that, New York is blanked.
Okay, now let's make a slight modification to this. Instead of the exit sub here, I just said that to be quick, what we're gonna do is we're gonna put an else here. Okay, we're gonna say else, it's got a value, so do that. Right, set the state combo row source equal what we need it to be and then end if.
And then when you're done updating the state combo, I want you to now update the next one in the series. So update state combo is now gonna say update county combo and then now county combo is gonna do the same stuff, right? If IsNull the guy above it, so state combo, then my stuff, right, blank my stuff out. So county combo equals null, county combo.RowSource is blank, else, do that, right, end if, and then when you're done, update the city combo. Because what's gonna happen is if they blank the country, it's gonna come into here, it's gonna null that, update the county combo, it'll come down here, it's gonna say, oh, the county's null, it's gonna blank that, it's gonna come down here into the city, and then so forth and so on down the line. So if you blank the country, every other one will be blanked.
So now it's just finishing up copying the code, and so we've got If IsNull, and trust me folks, type it out. You'll, one of the things that I remember helped me learn a lot as a young guy and as a child is actually, actually, we still had to buy books, right? And we had to type this before they had CDs in books, people. We just have to type in the code from the book. I remember I used to get a Color Computer, a Tandy CoCo. I used to get the little magazine, and you type in the code, typing in the code, it goes a long way to helping you learn it. It's better than just copying and pasting from some page you found online, including my own.
All right, so we're gonna update the city combo. So if the guy above city, if the county combo is null, then city combo is null. And city, and it'll make you a better typist too, unlike me. That.RowSource equals blank, else do that, end if, and then update the last one, update street combo, last one, almost there.
All right, if IsNull the guy above me, the city combo, then street combo equals, and see, by the time you're down to the fifth one, now you kind of remember it without having to look at the previous one. Street combo.RowSource is blank, else, tab that in, end if, and then we're done.
Okay, this guy's not gonna update anybody after him. All right, save it, debug, compile, once in a while. Oh, debug, compile once in a while, always looking, come back out here, close it.
I always close my forms and reopen them whenever updating the VBA code inside them. All right, back inside, let's set one up here. We got United States, we got New York, we got Erie, blah, blah, blah, and now it's blank United States, and they all get blanked.
Okay, so that's working. One other thing you might want to do, notice this is leaving the record dirty over here, when you open it up, even because it's checking this and it's blanking all those, that last one for the street, let's put a Me.Refresh here. I'll save that, so now when you open it up, at least you're not staying in a dirty state. See?
Now that takes care of the null problem, but it doesn't take care of necessarily the following combo boxes problem if you've got something like this going on. Right, and if I change this to Canada, it's not, it's blanking this one, but it's not changing that guy. So I'm also gonna throw in code, in fact, what we're gonna do, I'm gonna call an audible, we're gonna move this, I'm changing from my original game plan here, I think it's gonna be more efficient. We're gonna move that null down here, right, because no matter what we do, whether we blank it or reset it, we're still gonna want to have to put a new value in it.
Okay, the exception being sometimes people click on a box and then pick the same thing, so you could get around that by saying, if the box above it is different from what was in there before, then blank this guy. So right here, for example, I could say, if country combo is not the same thing as country combo.OldValue, then OldValue is a property, and I spelled country wrong, see? See how I immediately saw that because it didn't camel case right? Country combo.OldValue is a property that combo boxes have, that text boxes have, a lot of controls have it, so you could see what was in there before the user changed it. And now we'll just take this and just move this into the other ones. We'll get rid of this null value here. So this is county, and we'll put it right down here.
All right, so if the one above it, if state combo is not the same as state combo.OldValue, that, and county combo is not. All right, we'll do the same thing for the other ones. So we'll copy, let's see here, come team. All right, we'll get rid of that, and then we'll copy and paste.
Right, if county combo is not the same as the old county combo, then city combo is not. And then down here, get rid of that.
And we'll copy and paste this guy, and we'll stick that there. And we'll say city combo, city combo, street combo.
Okay, that should fix both of those situations. Save it, debug, compile. Come back out here, close it, open it. All right, so let's pick United States. That should blank everything. I'll go Florida, Lee County, Cape Coral, whatever.
Switch this to Canada. Oh, it didn't get everybody. If you see that happen, that's usually because I probably spelled something wrong or put the wrong combo box value. Let's take a peek. No, no, okay. So the audible that I called was wrong, and I just threw an interception, because checking for this old value only works on the one that was just clicked on, because the other ones are obviously going to have the same value, so they won't be reset. So unfortunately for this, you could probably put some advanced logic in there to check in the actual after-update event itself, but no. So I think we're just gonna go back to what I had initially planned, which is this, because right after you finish the, after you do the first one, it works fine. Hope someone's beaming in.
All right, but when you get to the second one, this condition will always be false or true. No, yeah, this will be, this will be false because the values will be the same, but this should fix it. So you're just gonna just blank them, and if the user does something dumb like pick United States and then click on it and pick United States again, well, and they got to re-enter all your data. You could cancel the event with a Before Update, right? And there's lots of ways you could go about fixing that, but for the purposes of class, to get this working, we're gonna just skip it.
Okay, so now if I pick Canada, it blanks everybody under it. If I pick United States, it blanks, should blank everybody underneath. Let's see. Ready, if I change it to Canada, everybody's blank. Okay, that's what I initially had in my outline, and then I thought I was gonna be cool and sneak in that old value trick.
The next thing I'd like to do is after I pick a country, I want the state box to open up for me. Right now, we can't put that in our functions or in our subroutines we've got written here because these run down each other one, so this is something we'd put in the After Update event. So country combo, after you update the country combo, drop the state combo box down.
All right, in order to use drop down, you have to set focus first. So State Combo.SetFocus, State Combo.DropDown. So it's gonna move to the state combo and then open it up, right? Save it. Close it, open it. So now if I change this to Canada, boom! The user's automatically presented with a list of states. That's just user enhancement right there. What's it, UX they call it now, user experience, right?
Obviously, I got a video on SetFocus and, ooh, I don't have a video on DropDown yet. Looks like I gotta make one. Miss on that one. I know I've covered it in other videos before, Quick Queries 24, Words: The Surveys video. A lot of these topics, I've covered them in other videos. I just don't have a video specifically on that, but that's my goal. I want to have a video for every topic that's access-related. So if you search for something on Google or YouTube and one of my videos doesn't show up, I want to know what you searched for. Send me a copy of your search string and I'll make a video for it if I don't have one already. But I have covered it for my students in Access Developer Level 2, so you've learned that.
But anyways, now all we have to do is put that in the other one. So we got country and I'm gonna put these in order too. So where is country, where is state, these come in here usually in alphabetical order, but I like to keep them all together. So country, state, county, then we need city, where's city? Come here. There's city. Let's grab you, put you after county. There. And then street would be in the bottom. I don't think we have a street update. Oh, we'll do that too.
All right, so there's country, then we've got, I'm just gonna copy these. Copy and we're gonna go paste, paste, and paste. Nope, not in the form current, nope. All right, so we've got country, does that. State is going to do Update State Combo and then go to it, or county combo, sorry. County combo is gonna update city combo, I forgot the copy and paste. All right, city combo, and then city combo, same thing with street.
Okay, easy enough. Debug, once in a while, come back out here, close it, open it, and ready? United States, New York, Erie, Amherst, Millersport, see how fast that was? Yeah, fast that was. Right there, that's a time saver.
Now what to do after street? Well, we got a street number field. I'm gonna put that over here and then after you enter that, jump over here or whatever you want to do, right? So I got a zip code sitting over here floating. I'm gonna copy that label, click on zip code, and paste it. That way it locks it to it and I just now made that the label for that field, right? Because even if you're doing this for other purposes, demographics or population or whatever, you still might want their zip code, right? And you might still want their street number. So over here and then we'll call this the street number and open that up, the street identifier, whatever we called it before, right? And this will be the street number, copy, paste. And then what we'll do in the after update for street combo, after update is we'll just jump to street number, right? So street number.SetFocus and that's really all you got to do. Save it, close it, close it, close it, open it, right? And oh look, I just discovered another problem. This is what happens when I go off script. We have to move this. This can't happen in the cascade down. This has to go in the After Update event as well because what happens is when you open it, it's gonna update the country information, which means it's gonna update this and automatically blank it for any record that you open. That's in the wrong spot. So this has to go in the After Update event for the country box. So let's get rid of all these. My bad. See, this is why this is what I try to throw audibles sometimes, two interceptions in one game, and I'm gonna get pulled. They're gonna send me to the bench. But I like to leave my mistakes on my videos because A, I make mistakes too, a lot, and B, mistakes that I make are probably gonna be mistakes that you make too. I'm not infallible. Don't tell my wife that.
So after the country combo is updated, update the state combo, but blank it here. So state combo equals null at that point. And you're also gonna want to null the ones after it, all right? So County Combo equals null. And let's go under county, City Combo equals null, Street Combo equals null. All right, now you're gonna want one less for everybody below it. So copy those. After you update the state, we're gonna do that. And then after you update the county, we're gonna do that. And then after you update the street, we're just gonna do that.
Okay, all right, save it, debug, compile. Let's see if it works this time. My bad.
All right, New York, Erie, Amherst, whatever. Close it, open it. All right, we're good. Change this to Canada, Alberta, don't have anything for Alberta, come on there, yeah. Niagara Falls, we're good. Close it, open it, we're working.
Okay, good. I'm sure some of you will find some additional bugs. They're always in there somewhere.
All right, that's a winning solution. And as you can see, as I finish Millersport Highway, it jumped me over there. Okay, we got it working. I'll follow my outlines more closely next time.
All right, so what's next?Well, what if your user wants to add something and you want to let them, right? Do you really want to give them that? I mean, you could give them this thing. That's kind of confusing. Wouldn't it be nice to have an easy way to just edit this list right here?
Wouldn't it be nice if they could just come in here and hit this little button and go, "Oh, look, I can edit the states for the one that I'm on. Oh, and then I can come in here and go, 'Oh, let me edit the counties.'" Oh, look at that! Well, we're going to do that in the next class, which will be part five.
Now, tomorrow is Friday, the 28th of March 2025, and Fridays I've been making Quick Query Days, and we're going to continue that tradition, so the next class will be on Monday, the 31st. There we go.
So if you're watching this in the future, you can go watch it right now, but if not, you have to be a member. Members can watch it whenever they want because I'm going to keep recording right now, so you can watch it right now if you want. That's one of the benefits of being a member.
Also, members, we're going to have a little extended cut after today's video. Remember I said I think it was yesterday I mentioned that all of this code, every one of these updates, state combo, update country, all that can be condensed into one.
Well, I did it, and it's called UpdateCombo, and then you just send it the combos, the regular combo and its parent, and then that's it, and it works. We're going to go over that in the extended cut for the members. Silver members and up get access to all of my extended cut videos. Gold members can download these databases, and everybody gets some free training, so check it out.
That is going to be your TechHelp video for today. That's all for part four. Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for Quick Queries number 33, and we'll be back on Monday with part five. Have a good weekend.
TOPICS:
Cascading combo boxes setup
Organizing combo box order of operations
Clearing dependent combo boxes
Using IsNull function in VBA
Updating combo boxes automatically
Using RowSource property in VBA
Setting combo box values to null
Using else statements in VBA
Checking OldValue in combo boxes
Using SetFocus and DropDown methods
Updating combo boxes in After Update event
Adding Me.Refresh to avoid dirty state
Adding zip code and street number fields
Debugging and adjusting VBA code flow
Plan for a combobox edit feature
COMMERCIAL:
In today's video, we're continuing with part four of our series on multiple cascading combo boxes in Access. If you're new, make sure to catch parts one through three before joining us. Today, you'll learn how to make sure your combo boxes reset correctly when a selection is changed. We'll go over organizing combo box updates with VBA, handling null values, and making your form more user-friendly with set focus and drop-down enhancements. Plus, we'll clean up your form so it operates smoothly without leaving dirty records. Also, if you're a member, stay tuned for an extended cut where we simplify our code with a neat trick called UpdateCombo. 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 was the primary focus of this video tutorial?
A. Building a SQL database from scratch
B. Creating multiple cascading combo boxes
C. Debugging and compiling code in Access
D. Designing a new user interface with forms
Q2. What should happen to the dropdowns when the user changes the country combo box?
A. All dropdowns should remain unchanged
B. Only the state dropdown should reset
C. All the dropdowns beneath should be reset to blank
D. Only the city and street dropdowns should reset
Q3. What method is used to check if a combo box value is null?
A. IsBlank
B. IsEmpty
C. IsNull
D. IsVoid
Q4. What is the significance of setting focus with SetFocus in this tutorial?
A. To highlight the entire form
B. To enable the user to quickly start interacting with the next combo box in the sequence
C. To attract the user's attention to a button
D. To update the form title with the current combo box selection
Q5. Which method should be used to open the dropdown list programmatically?
A. ShowDropDown
B. OpenList
C. RevealOptions
D. DropDown
Q6. Why was OldValue used in the tutorial?
A. To compare the new value with the previous value of a combo box
B. To display a warning message if a combo box was accidentally cleared
C. To reset the entire form to previous values
D. To permanently store old values for future reference
Q7. What action does Me.Refresh perform in the context of this tutorial?
A. It clears all combo boxes on the form
B. It updates the form's data source to reflect new data
C. It resets the form to its initial state
D. It ensures that there are no unsaved changes displayed as "dirty" on the form
Answers: 1-B; 2-C; 3-C; 4-B; 5-D; 6-A; 7-D
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 fourth part of our series about creating multiple cascading combo boxes. If you have not yet viewed the initial three parts, I recommend starting there before continuing with this discussion.
Yesterday, we successfully set up cascading combo boxes. You can select options like United States, California, or other states, though our example didn't cover every detail for California. You might see choices like New York, Erie, Amherst, and Sheridan. As a side note, I used to live near Sheridan Drive in Millersport.
Today's tasks include handling situations where, if a user changes their country selection (for instance, to Canada), all subsequent related fields should reset to blank, as they are no longer applicable. Additionally, if a user selects an invalid option, it can lead to an error, such as an SQL statement with 'country ID equals null'. We need to correct this issue as well.
In our code design view, I've added a button that allows easy access to the code for the form—an incredibly useful feature I frequently use. We begin by organizing the elements based on their sequence of execution: state, county, city, and street.
For the update to the state combo box, we can check if the country selection is null, which means it should blank out the state combo box, and do the same for other dependent boxes like county and city by removing their row sources and proceeding accordingly.
After testing, picking United States and subsequently blanking it should blank all dependent choices. I then modified the code to eliminate redundancy. Instead of immediately exiting the procedure once a field blanks, I included further instructions to update the following combo box in sequence.
The logic is sequential: if a parent selection becomes null, its children must also null. Typing the code manually helps memorize the process—and I cannot emphasize enough how much this practice aids learning over merely copying and pasting.
Upon completion of updates to each combo box—from state to street—I thoroughly tested each condition to ensure they behaved correctly, including implementing a Me.Refresh to prevent leaving the record in a dirty state after changes.
I considered using a more efficient method with an 'OldValue' property to check for changes but realized its limitations when other fields naturally had differing or unchanged values. Thus, it couldn't function effectively in all scenarios, so I reverted to the initial straightforward solution.
Once the country is selected, the state combo box will now automatically drop down, enhancing user experience—a tweak implemented via the After Update event of the country combo box. Similarly, after updating selections like street, focus shifts to associated fields like street number.
Lastly, I discussed future improvements, such as allowing users to easily modify lists from within the interface, a feature planned for part five. Though Fridays are reserved for Quick Queries, the next session on cascading combo boxes resumes on Monday.
For members, today's extended cut will include consolidating all our code refinements into a single procedure called 'UpdateCombo', a neat solution simplifying the handling of multiple cascade dependencies. Silver members can access extended cuts, and Gold members can download databases.
This concludes today's TechHelp tutorial. More detailed, step-by-step instructions can be found in the complete video tutorial on my website linked below. Live long and prosper, my friends.
Topic List
Cascading combo boxes setup
Organizing combo box order of operations
Clearing dependent combo boxes
Using IsNull function in VBA
Updating combo boxes automatically
Using RowSource property in VBA
Setting combo box values to null
Using else statements in VBA
Checking OldValue in combo boxes
Using SetFocus and DropDown methods
Updating combo boxes in After Update event
Adding Me.Refresh to avoid dirty state
Adding zip code and street number fields
Debugging and adjusting VBA code flow
Plan for a combobox edit feature
|
