Multiple Cascading 5
By Richard Rost
11 months ago
Make Multiple Cascading Combo Boxes in Access Part 5
In this Microsoft Access tutorial, I will show you how to manage multiple cascading combo boxes by creating a series of small list item edit forms. This is part 5 of my series. You will learn how to efficiently add, edit, and delete entries for countries, states, counties, cities, and streets using separate forms, ensuring that combo boxes reflect appropriate selections dynamically. We'll also cover handling default values and using hidden fields to maintain parent-child relationships between records. Additionally, I'll demonstrate a method for preventing user inputs when the parent reference is missing. This is part 5.
Members
In the extended cut, we will create one big table to store all of the related data, including countries, states, and cities. I will show you how to set up an address level table for titles while consolidating all the data into a single main table. This approach will be similar to the helper data series, providing a more streamlined method of managing geographic information. The extended cut will be made available to Silver members and up, offering a structured approach to managing multiple cascading combo boxes with a unified data table.
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!
Update
- I answer a lot of the questions asked by students in this series in Quick Queries #34.
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, cascading combo boxes, list items edit form, edit records button, hidden fields, default value property, "Not In List" event, modal forms, parent-child relationships, troubleshooting forms, row source modification, on open event, NULL value check, combo box filters, practice coding forms, extended cut tutorial
Intro In this video, we continue working in Microsoft Access with multiple cascading combo boxes by building dedicated list item edit forms for each dropdown, including country, state, county, city, and street. I'll show you how to ensure that parent-child relationships are maintained when adding or editing items, including setting default values and hiding fields as needed. We'll also cover how to use the form open event to validate user selections and prevent errors if a required parent item is missing. This is part 5 of the series.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. This is part five of my multiple cascading combo boxes series. What does that mean? Well, go watch parts one through four, and you'll figure it out. So here we go.
All right, we've got our cascading combo boxes working just fine. If you pick a different one, like Canada, it resets them. We've got Ontario and Niagara Falls and so on. Now, what if you want to change, add a state, delete something, or whatever? Making changes at this point involves either editing at the table, which we don't let our users do, or we can go to the big giant... what is the thing here? Where is it? Country, there you are.
This big thing is fine for the admin or for adding stuff the first time or if you need to come in and make a tweak. But it's a bit of a pain for the end users. Now you can use a list items edit form. You could drop them on the specific one of these if you want to, like this. If you don't know what a list items edit form is, go watch this video. It's basically where you get this little button down here, and you can specify another form that you can open up and you can edit records, add records, delete records, or change the stuff in the combo box.
You could do that for each of these forms that we already have built. Let's say, for example, county. You can come in here, go to the data tab, and go to list items edit form right there, and send them to the CountyF, like this. County, go to there. That's not too bad.
But there is a problem. What if I want to add a county? First of all, you're seeing all of the counties. You're seeing every county in the whole thing. If you add a county, watch what happens. Let's say I want to add, you know, Smith County. Well, what state's it in? If you go to the table list of counties, it's got a zero in it because it doesn't know where to get the parent from. It's a mess. I don't like using that.
So what we're going to do is create a series of five small list item edit forms for each of these combo boxes. Nice and simple. Let's start with the country, of course. Find our country form, and we're going to copy-paste and call this guy country edit form. Country edit form. Right-click, design view. Now get rid of all of this; we don't need it anymore. We're not putting sub-forms of this. We're going to have one for each of the combo boxes.
Keep it the same color to make it look similar, so the users are familiar with it. Save it, close it, and then we'll set that as the list items edit form or country. This is the easy one. Save it, close it. This one doesn't have a parent. If I want to add a country, ah, if I do that, chances are you'll do it. That's the country edit form. That was my bad. Take two.
If we want to add a country, we just come in here, and there it is. You can resize it. Keep in mind, list items edit forms always pop up as modal, so it only really makes a difference when you're designing because you have your navigation pane open, but you always want to hide that for your users. You can come in here and add another country. What do you want to add? Let's add Jamaica. I'm not... there we go. Love Jamaica. I've been there like four times.All right, and now there's Jamaica. Is there a way to have it so that value gets sent right back? It's tricky with list items and forms. There are better ways to do that. I've covered that in some of my developer classes. Using a "Not In List" event is actually more powerful, but this is good enough and it's simple to implement. You see how easy it is: you just add the item, come back here, and it'll be in the list. All right, that works. That was actually a great addition when they added that to Access. I think they added it in like 2013. Before that, we didn't have that feature. You had to know how to program a "Not In List" event, or you had to close this, open up another form, and go back to it. It was a real pain. I remember how excited I was the first time I learned how to program a "Not In List" event.
That was the easy one because country doesn't have a parent. States have a parent, so we need to know what parent we're opening and what country you want to see states from. Let's find our state form, copy, paste, state, edit, open her up, design view, and get rid of the subform. Get rid of that. Okay, now here's the thing: I only want to see states for the country that's open on the customer form. Okay, so the row source or the record source, excuse me, instead of just StateT, it's going to be—I'm going to zoom in so you can see it better—it's going to be "SELECT * FROM StateT WHERE CountryID = Forms!CustomerF!CountryID" or "CountryCombo." Both work as long as that field is in the underlying form's dataset. It'll work, all right. "ORDER BY StateName." Okay, hit okay, save it, close it, close it, and then we've got to set the List Items Edit Form for state, which will be right there. I shouldn't close that. This will be "StateEdit." Save it, close it, close it, open it, drop her down, hit the button. Okay, good, the State Edit form for just Canada, but we still got the same problem we had before. If I type in something new and I close it and I come back in, it's not there. It's gone. What happened? Well, anybody see? We talked about it a minute ago. Look at that: it's got a zero for CountryID. So we've got to get a way to populate that value into this form.
How do we do that? Well, I'm gonna use a hidden field. So I'm gonna go into my State Edit form design view. I am going to go to the Add Existing Fields and find CountryID and drop it up here in the header. Delete the label that comes in with it, and this will be a little hidden field up here. So what I'm gonna do is close this. Now I'm going to set it so that its "Visible" property is false. I don't want to see it. And usually, when I make invisible fields like this, I format them red. That way, when I go in design view, I can just see that it's there, and then all I add is the hidden field.
Now its data is going to be in the default value here equal to "Forms!CustomerF!CountryCombo." So when you add a new record, it's going to get the default value from the customer behind it. Got it? All right, so now if I come in here and go to add and want to add a province, let's add New Brunswick. Okay, and now we'll close it, and then it's on the list. All right, and now we learned; now we do the practice, and I'll do the same thing for the county. All right, so county, copy, paste, it's gonna be "CountyEditF," edit, design view, delete the subform, slide that up. We're gonna change the data to "SELECT * FROM CountyT WHERE StateID = Forms!CustomerF!StateCombo ORDER BY CountyName." Hit okay. We're gonna add the hidden field now, so Add Existing Fields, StateID, delete the label, make this guy red, slide it up, make it invisible, format "Visible No," data "Default Value = Forms!CustomerF!StateCombo." Save it, close it, drop down county. I'm gonna go over to the United States because I know more of these. Let's go back to the United States. Let's go to New York, County Erie. Let's add another county here, hit that. Oh, I put the wrong one. This should be "CountyEdit." Okay, my bad. All right, Erie, look on that, there we go. And you can resize these whenever you open them like this, right? Move it over here, hit Control-S to save its location. Then we'll add Monroe County. Okay, close it, and there we go. All right, continuing on, we got our city next. So CityF, copy, paste, CityEditF. Edit, design view, delete that, slide that up. Open this record source. It's gonna be "SELECT * FROM CityT WHERE CountyID = Forms!CustomerF!CountyCombo ORDER BY CityName." Do a couple of them, and you get the practice. You don't think I get this good to do in deal look-ups and stuff by just having done it once or twice. I do millions of them, right?
All right, then our hidden field for the CountyID goes right there. Delete, yeah, you could copy and paste this from the other fields, just change the thing, but again, that defeats the purpose; I want you to practice this stuff. Okay, so our "Default Value = Forms!CustomerF!CountyID" or "CountyCombo," either one. Keep it consistent: if you pick one for one, pick it for all. Let's make that red and not visible. Save it, close it, drop it down. Got nothing there. Oh, I got to refresh the... oh, I didn't set it in here to "Data List Item Edit Form." That's why I always like to close and reopen the CityEdit form. Close it, close it, close it, open it. All right, Monroe, we're gonna add somebody. Let's add a Rochacha, Rochester. Okay, then you gotta add it; there we go.
And finally, street. Street form, copy, paste, StreetEdit. You know what, now I don't want to. I want to stay consistent, but Street form doesn't have a subform in it, so you literally could just drop the hidden field on it and use this one form as both of those things. But again, I want to remain consistent just in case in the future you decide to do something different, and there's no harm in having two forms that are very similar or almost identical. Okay, so that's that. Oh no, there is harm because you have to change the row source. I'm sorry, but keep that in mind for the future, right? Because this guy needs to be able to pick just the streets for the city it's in. So "SELECT * FROM StreetT WHERE CityID = Forms!CustomerF!CityCombo ORDER BY StreetName" and then okay.
And then the hidden field, this guy, drop it there. Delete the label, make it red, make it invisible. Format "Visible No," and data "Default Value = Forms!CustomerF!CityCombo." Save it, close it, and set it in here. The last step, and I always forget about it: "Data List Items Edit Form," "StreetEdit." Save it, close it, close it, open it, and Main Street. Pretty sure there's a Main Street in Rochester. Okay, and, oh, what's not there, what happened, what happened, what did I do, what did I do? Street, oh, it didn't link it. Let me see here, hang on, type on maybe, what did I do, anybody catch it? Hmm, that's going to be in the Edit form. Design, CityID, yep, table; there it is. Custom Combo and City Combo, I'll do it.
Well, that's the problem right there. Can't get there from here. Beautiful, I need a beer now. You see, a lot of it's practice though, I can teach you something, but you got to practice it. There's a lot of practice involved with that one. All right, we're going to do one more thing, and I think we're gonna call it a day on this. Let's say someone goes to a blank form, doesn't pick a country, and then tries to go into the List Items Edit form, and then they give this. They put something in here, and that's not going to show up on the list. And again, same problem, right? If you go to, what was it, a state? Okay, that's not on the bottom, the zero. So we have to put in each of these forms: don't let the user do anything if the parent item that we're looking for is null, because this can either be a valid value or a null. So in each of these forms, starting with state, you don't have to do country, we're going to basically, when the form loads, we're going to check to see if its parent has a value, and if not, we're going to say, "yeah, can't do that."
We're going to use the form open event. So let's open up a customer form and let's just go to someone whose everything's blank, and in the StateEdit form, not street, state, I did that, I made that mistake in earlier when I was playing with it. StateEditF, design view. All right, look at your form properties, go to the "On Open" event. Where are you, "On Open"? Remember the difference between open and load is that open can be canceled. All right, so in here we're going to say, "If IsNull(Forms!CustomerF!CountryCombo) Then message box," whatever you want to say to them, "Select a country first," and then here's the important part: "Cancel = True," and then we're done. So the form is going to open, it's going to check the value of that combo box. If it's null, it's going to say, "can't do it," save it. I'm going to copy this to my clipboard because we're going to copy it in other places in a minute, copy that. All right, debug, compile once in a while, close this, close this, and now I can try adding a state when there's no country. Can't do it. Same it. Okay, and if you want to you, can even add more code to have this guy drop down or whatever you want. You've seen how to do all that. All right, so that's that.
Now it's time for practice. We've got county, where's county? County's right here, design view. Whoa, nope, nope, see I have made that mistake too. CountyEdit, design view, and this guy's on "Open" event, right there. We're going to paste that code. We're looking for the State Combo and "Select a state first." We'll do them all while we're in here, right? Close that one. After county comes city, city up here. CityEdit, design view, on "Open" event, and then paste. We're looking for the County Combo. All these names are going around my head. Crazy looking for our county before we can pick a city. All right, and then finally, we've got the StreetEdit, where, yeah, StreetEdit, design view, on "Open" event, paste. And then we're going to look for the City Combo and "Select a city first." Save, the bug, compile, close it, close it, and try picking a street. All right, try picking a city. Man, try picking a county. Man, try picking a state. Man, okay, fine, I'll pick a country, Canada, Ontario, Niagara Falls, you get it, and it works. Okay, and if you blank one of these, it blanks everybody under it. We're good. I think we've covered pretty much all the basics, so that's about it for this little mini-series. I hope you learned something. I do cover cascading combo boxes in a lot more detail in my Access 2 Developer Level 6 class. We do a lot of stuff with product categories and stuff like that.
Now, members, I mentioned this in the extended cut after Level 4. We are going to have an extended cut following this video. I told you briefly what it's gonna entail. We're gonna basically use one big table to store all of the related data, all of the countries, all of the states, all of the cities. It's all gonna go in here, kind of like my helper data series. We'll have an address level table just to have the titles in here, but all of the data is going to be in one big table. Now, if you're watching this video live because this video goes public on March 31st, 2025, I have not recorded this yet, and I'm going out of town for a week. So I'm going to record this when I get back, which should be before this video goes public, but the members get to see this stuff as soon as I post it, and I'm posting this on Saturday the 22nd, so you're gonna have to wait a week or so to see this extended cut because I haven't recorded it yet, but I'm gonna be recording it when I get back. Okay, so that's what's gonna be covered in the extended cut for the members. Silver members and up get access to all of my extended cut videos, not just this one, all of them, and there's hundreds of them, there's lots of them, and everybody gets some free training and lots more cool stuff and me. You get more of me, so that's a plus, usually.
But that's gonna do it for this little mini-series. I hope you enjoyed this. I had a lot of fun. People constantly ask me this, "Well, how do I do a third combo box?" Well, now you know, and knowing is half the battle. But that's your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Cascading combo boxes setup
List items edit form creation
Country combo box edit form
State combo box default value
City combo box setup
County combo box edit form
Street combo box setup
Populating default values in forms
Form open event for validation
COMMERCIAL:
In today's video, we're continuing with our multiple cascading combo boxes series. This is part five. We'll learn how to create simple list item edit forms for each combo box, such as country, state, and city. I'll show you how to handle adding new items without messing up the parent-child relationships. We'll also cover strategies for preventing errors when users attempt to make changes without first selecting the necessary parent items. Plus, I'll share a tip for hiding and managing default values in the background. And for those curious about more advanced features, stay tuned. 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 purpose of creating small list item edit forms for each cascading combo box?
A. To make the combo box lists editable directly from a table by users
B. To provide an intuitive way for end users to add, edit, or delete items without accessing the main table
C. To allow admin users to edit all data simultaneously
D. To ensure each combo box has a separate sub-form for complex data relationships
Q2. What problem arises when adding a new item to a combo box without the correct setup?
A. The new item is automatically added to the main table but not displayed
B. The combo box doesn't refresh, leading to multiple entries
C. The new item may be added with a zero for the parent ID due to lack of context
D. The form crashes and has to be restarted
Q3. How does Richard solve the issue of new records being added with the wrong parent ID?
A. By setting a default value for a hidden field on the edit form based on the combo box selection in the parent form
B. By manually entering the correct parent ID every time a new item is added
C. By using a completely separate table for new entries
D. By allowing users to select the parent from a drop-down menu each time
Q4. Why is the "Not In List" event more powerful than list item edit forms?
A. It automatically updates the database and refreshes the combo box in real-time
B. It allows users to select any item from the list without restrictions
C. It provides a more customizable user interface
D. It requires fewer resources and less programming knowledge
Q5. What should be done to prevent users from adding entries to an edit form when the parent combo box value is null?
A. Use a validation text box to give warnings
B. Automatically redirect users to the correct form
C. Employ a message box prompt and cancel the form opening using the form's "On Open" event if the parent value is null
D. Lock the combo box to prevent any interaction
Q6. What is Richard's advice if the user interface doesn't reflect added data as expected?
A. Always restart the entire application
B. Close and reopen the form after setting the list item edit form option correctly
C. Use an admin panel to manually adjust data entries
D. Ensure all form colors are matching for the best visual effect
Q7. What was the initial method for handling combo box data before the feature added to Access in 2013?
A. Changing data by opening the main table directly
B. Using the "Not In List" event or use of intermediate forms
C. Employing external database management software
D. Implementing a real-time update script
Answers: 1-B; 2-C; 3-A; 4-A; 5-C; 6-B; 7-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 AccessLearningZone.com focuses on the fifth installment of the multiple cascading combo boxes series. For those not yet familiar with cascading combo boxes, I recommend reviewing parts one through four to gain a solid understanding of the concept before continuing.
Currently, our cascading combo boxes are functioning as designed. For instance, selecting Canada appropriately adjusts subsequent box options to relevant locales like Ontario and Niagara Falls. However, if adjustments such as adding or deleting states are required, direct table editing, which is restricted for end users, becomes limited and cumbersome for administrative users.
A more efficient approach involves employing list items edit forms to facilitate user interactions. These forms enable users to manage records within combo boxes—adding, editing, or removing items. By integrating this functionality, we enhance usability without compromising data integrity. Suppose we're focusing on modifying counties; by navigating to the data tab of the intended form, the list items edit form can be redirected, such as to a CountyF form, providing a streamlined process.
Yet challenges remain. Adding a new county, for instance, without correctly associating it to a state, results in database issues because the system fails to assign the new entry a parent identity. To resolve this, I'll detail a solution involving the creation of several small, specific list item edit forms dedicated to each combo box. We'll begin with the country and systematically create, modify, and manage a similar structure for states, counties, cities, and streets.
Starting with the country, a straightforward edit form will be created by duplicating existing forms, adjusting design for user familiarity, and eliminating potential clutter. Each form needs specific configuration to reflect changes effectively and maintain a database link for consistency.
Transitioning to states, which have a parent dependency, requires precise configuration to display relevant information only for selected countries. I'll demonstrate how to modify the record source to implement this responsive interaction and then apply a default mechanism linking new entries to existing parent forms.
Subsequently, analogous methods will be deployed for counties, cities, and streets, ensuring each can link appropriately to its hierarchical predecessor. Through practical examples, the utility and function of each form is highlighted, illustrating how seamless transitions can be achieved within the database structure.
Moreover, to enhance the overall user experience and prevent null value discrepancies, each form's logic includes validating parent field selections before allowing data entry. This ensures that users receive immediate prompts when hierarchical selections are incomplete, maintaining database integrity and simplifying the user process.
Practice is key in mastering these techniques. While a systematic approach has been described, repeated practice with list item forms and database link configurations enriches understanding and proficiency. This structured method enhances functionality substantially without increasingly complex setups.
Additionally, for those interested in further expanding on this framework, I've covered comprehensive cascading combo box strategies in my Access Developer classes. These provide an in-depth exploration into advanced uses, including product categories and more, extending beyond the foundational cascades discussed here.
Also, an extended cut will be made available, focusing on consolidating related data into a single comprehensive table, much like my helper data series strategy. This recording will be available shortly, allowing members access to refined techniques and added depth in handling complex data scenarios.
In conclusion, this mini-series elaborates on constructing and sustaining sophisticated combo boxes for enhanced user experience and data management. If this process sparks questions about expanding beyond a third combo box, you're now equipped with critical insights to progress further.
This tutorial concludes today's session, with more detailed, step-by-step video instructions accessible through my website linked below. Thank you for joining me. Live long and prosper, my friends.Topic List Cascading combo boxes setup
List items edit form creation
Country combo box edit form
State combo box default value
City combo box setup
County combo box edit form
Street combo box setup
Populating default values in forms
Form open event for validation |
