Quick Queries #34
By Richard Rost
8 months ago
Cascading Combo Q&A, Not In List Handling, More!
In today's Quick Queries tutorial, I will address a wide range of questions from viewers, focusing on topics beyond the standard scope of queries in Access. We'll begin by discussing multiple cascading combo boxes and the different ways regions and administrative divisions are handled across various countries. You'll learn how to store and manage data efficiently even with partial information. Additionally, I will explore user questions on employing wildcard options, handling missing geographical data, and optimizing form input with relational data. We'll also cover some fun, practical solutions, and highlight community feedback, offering insights into various user-submitted questions.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, cascading combo boxes, multiple cascading combo boxes, combo boxes tutorial, relational data in Access, handling partial data in Access, Access queries vs questions, cascading dropdowns, Access multiple subforms, Access form editing tips, Access Developer 35, Access not in list event, VBA workflow in Access, Access conditional logic, Access table normalization, Access class module, April Fools' database joke, Access data structure strategies
Transcript
Welcome to another TechHelp quick queries video. Episode number 34 is brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Did he get a bunch of questions? I'm going to answer them kind of like, what was that? Oh, Kindergarten Cop? I got a bunch of questions. How's it going? I want to ask you a bunch of questions. I want to have them answered immediately. Great movie! Anyway, quick queries videos are all about lots and lots of questions people ask that maybe don't need a whole video on themselves, and I put them together and call it quick queries. It's not always about queries, although sometimes it is.
I actually talked to one guy at the MVP summit, or sorry, at Access Day, and he said that he saw my quick queries videos, but he knows all about queries, so he never bothered watching them. He didn't realize it wasn't actually about queries. It's like a double entendre, right? Queries, questions? So if any of you were in that boat, well, now you know better.
I'm going to start off today with a whole bunch of questions and comments about my multiple cascading combo boxes video series. If you haven't watched this, go check it out first. Multiple cascading combo boxes are where you can pick a value in a combo box, and the ones after it are based on the one before. So you pick a country, then you pick a state, and you get the list of states from that country, the list of counties from that state, and so on down the line. We did five, oh, you can do as many as you want, but we did five.
First up, Jade Dragon says Ontario does regions like they do in British Columbia. You want to get more confusing; consider Louisiana, where they have parishes instead of counties, or Texas and their 254 counties. Different regions call them different things.
There's really no reason you couldn't make a separate table or, in your existing tables, have what it's called. For example, if you pick Canada from this list, it could read what Canada calls its states and update this caption. That's certainly possible. And if anyone wants to see how to do that, post a comment down below, maybe I'll make another video.
But yeah, I guess the United States is the only country that uses a zip code; everybody else calls it a postal code or something else. Counties are something different. I think cities are pretty universal. But yeah, that's pretty fun.
Always want to give a shout out to people who use the super thanks, so thanks to Bungee1962. I appreciate that. Thank you very much. And yes, for those of you who are curious, this is still a thing. I don't know why, but it is. I get them all the time.
Next up, Gregory asks an excellent question. He says, because of the one-to-many relationship, why does the customer record store the city, county, state, and country? Simply storing a street ID should relationally provide everything else. You are 100% absolutely correct. What he's saying is if I know that this person is in the United States, New York, Erie, Amherst, Miller's Port Highway, if I know Miller's Port Highway, I can figure out everything above it relationally. That is correct.
However, I set it up this way in case you only have partial information. What if you only know what state the person is in, right? You don't have all of this stuff. You can't put the street in. What if you only know their city? You'll have city, county, state, country, but you won't know what their street is. And so if you can't store the street ID, you won't know any of this information. Can you do it the other way? Absolutely, if that's the way you want to build your database, then by all means, please do.
You could make all of these unbound boxes, and just that last one is the one that stores the actual data in your database. This is storing some redundant information, and some people might even argue that it's not 100% normalized, but again, if you have partial data, then this setup is necessary. So excellent observation, Gregory.
Along the same lines, Cyberman says I was wondering about another possibility: What if someone would decide to put in a state or a city without first putting in the country? Is it possible to make Access fill it in for you as you switch to the next field? That's possible, but the way we have it set up now, you wouldn't see a list of states unless you first picked the country. What you could do is you could put a wild card, maybe an asterisk, in the country field, and if they picked that, it would show all of the states from all of the countries. That's a possibility. You could certainly do it that way, or you could just initially leave all of these boxes unfiltered and show all of them.
And if you really want, drop this down. If you know they're in the city of Amherst, drop this down, look through all the cities, type in Amherst, and it could fill in the ones above it. That's another possibility. Again, I'm just showing you different ways you could put them together.
If you'd like to see me make a video about that, post a comment down below, and if enough people are interested, I'll make a video. But again, excellent question.
Bullenti asks, how would you do this if there is no state or county in some countries? If we apply the same principle to employment in a company, there are different levels in different divisions like division, sector, department, team. Some have five, some have two. Well, the easy way that I would do this, the easy and cheesy way, is just put an "all" option in here. So for example, let's say a country doesn't have states or counties. They just have countries and cities. I would just put "all" in here and then "all" in there, and then those two options will filter down to your list of cities. That's the cheesy way.
The more complicated way would be to obviously have some VBA logic in here that would look and say, okay, if there are no states, right, or maybe if there's only one option, flag, you know, put it in there by default and then just bring it right down to city. Again, there are a million ways you can do it. That's the simple way to keep all the data the same. But you can obviously program exceptions in, right? If there's only one state, put "one" or "all," you know, "all" or "only" or whatever you want to call it in here, and then just drop the selection down to city. So again, if you'd like me to make a video about that, you know, put some comments down below, and I might make another video. But yet another great question. You guys are full of great questions with this video.
Same basic question here: Sometimes people get mad if I answer someone else's question, but they asked the same question, and I don't mention them, so I'm mentioning you. You get credit too for this question. But you basically answered the previous question. That's what I would do is just make an "all" option.
Rick L says, where can I get the lessons for not in list events that trigger a pop-up form, and when closed, puts that new data in the list box? Well, I cover a little bit of the on not in list event in the extended cut from my relational combo boxes video, and you'll remember, so you should be able to see this one.
As far as getting the value that was just added and adding it to the table, that's a little more advanced. I covered that in Access Developer 35. That's covered in this lesson here, where it'll actually prompt up and say, hey, enter the name of the new state that you want to add, like Virginia. And it's going to say it's not in the table. Would you like to add it? And then it'll put it right in the value. It's a little trickier, involves a bit of a VBA workflow, but it's not super hard. You can add the value behind the scenes either with a record set or a simple SQL statement, either one.
George asks, is there a way to put all this in a class and use the class in the form by giving the form and the controls an open form event? Is it possible? Yes, I will say I haven't even covered classes in my Developer course yet because in my 30 years of working with Access, I've never needed one. There hasn't been anything that I couldn't do without a class.
So I usually reserve that for object-oriented programming, like programming with C or something like that. But I am going to cover classes in my Developer course coming up just because a lot of people ask me about them, and there are some cool things you could do. Yeah, I guess you could do this with a class. I don't know. Probably, but is it the best use for it? Maybe not. I'll have to think or within let you know.
Mitchell says, what if you make a mistake and accidentally put a US state in for the wrong country? You pick Canada and put in Delaware. Is there a way inside the form to fix or delete it? Well, yeah, just go back into your edit form, your big multiple cascading or multiple subform. Right, those nested subforms, just go in there and change it, delete it out of one, and add it to the other one. Right, or go to the table, change the ID there. There are a million ways you could do it. You could set yourself up a separate form for each one where it's got the parent combo box right next to it. Right, set up a state form where you got a continuous form with all your states listed and have the country combo box be to the left of it, and you just pick the country there. They'll all be the same for every state, but at least you can easily edit it. Right, or you could even make one big edit form that's one giant continuous form of they're all together. Right, linked by a query, so there are a million ways you could do it. But yes, you can very easily.
Is there a way for the user to do it on the customer form? That's up to you if you want to give them that power. I wouldn't, but sure.
Moving on, it looks like a lot of you enjoyed my April Fools' joke. Thank you very much for all the comments. There was a little bit of heckling, although it turned into a database joke, Boolean, get it? Ha ha ha. A couple of you submitted jokes of your own. Why is Access so exhausting? Because you only have run time? Oh, oh, I didn't see that. You have my permission to use this for the cost of a mug. Oh, never mind, never mind. Goodbye. Mugs are available for purchase in my YouTube store. YouTube gives you the opportunity to merchandise stuff. I don't know what, none of this stuff really fits for me, merchandising anything. So if you go to my channel and pick store, there is a mug. Oh, I just picked a mug. I don't know, they put my logo on it.
If you guys want it, like shirts or any other stuff, I can do that too, but I didn't think anybody would actually want this. I think I sold one of these in like 10 years. I'll be honest though, I did buy one for myself. It's sitting on my shelf. And yep, see, people still do it. They still do it. It's okay. I don't mind. I'm glad some of you enjoyed the link that I had to click on.
All right, so that's going to do it for today's quick queries. They're not all rocket science, folks. Some of them are just for fun, but I'm glad you stuck around, glad you watched. That's your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Multiple cascading combo boxes
Handling different regional naming conventions
Redundant information storage in databases
Partial information in databases
Using unbound boxes for non-storable data
Wild card options for filtering data
Programming exceptions for missing levels
"Not in List" event handling
Class utilization in Access forms (discussion)
Fixing or editing incorrect data entries
COMMERCIAL:
In today's video, we have another quick queries session where we discuss a range of questions submitted by viewers. We'll explore topics like multiple cascading combo boxes and why certain fields are stored the way they are in databases. I'll answer questions from Gregory about data normalization and from Cyberman about Access filling in information automatically. Bullenti asks how to handle places without states or counties, and we'll cover that too. There's also a funny dive into whether you can utilize classes in Access. Plus, get some laughs from our April Fools' joke and hear about our merchandise options. Remember, this isn't a full tutorial, just a series of quick questions and answers. 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 primary focus of TechHelp quick queries videos?
A. In-depth tutorials on specific Access features
B. Answering various user questions compiled together
C. Only about database queries
D. Coding practices in Access
Q2. What do multiple cascading combo boxes allow you to do in Access?
A. Display multiple values in one combo box
B. Filter values in one combo box based on selections in another
C. Store complex macro functions
D. Enable dynamic table creation
Q3. Why does the instructor store city, county, state, and country information directly in the customer record?
A. To avoid using relational databases
B. Because Access cannot handle one-to-many relationships
C. To manage situations with partial data availability
D. For better performance
Q4. What is one suggested method for handling selections when a country does not have states or counties?
A. Excluding the country from the selection
B. Using an "all" option for states or counties
C. Creating a separate database for each type
D. Applying default values to all fields
Q5. In what scenario does the instructor suggest using VBA logic to manage selections?
A. When the database structure is too complex
B. When a country has multiple states or counties
C. When there are no states or only one option
D. To enhance the graphical user interface
Q6. What is the Access Developer 35 lesson about, mentioned in the transcript?
A. Setting up database relationships
B. Not in list event and automating data entry
C. Developing classes in Access
D. Creating a master-detail form interface
Q7. Why has the instructor not covered classes extensively in his Developer course?
A. They are too advanced for most users
B. Classes are irrelevant to Access databases
C. The instructor has found no need for them in Access so far
D. Classes are not supported in Access
Q8. How does the instructor suggest correcting a mistake, such as putting a US state for the wrong country, in the database?
A. Permanently delete the erroneous entry
B. Manually edit the entry in the form or table
C. Create an error log to track such mistakes
D. Use a programmed VBA script to auto-correct
Q9. What is the instructor's opinion on using classes for Access applications?
A. They are necessary for all complex applications
B. They can be useful but are not always necessary
C. They make Access run slower
D. They are only for beginner-level applications
Answers: 1-B; 2-B; 3-C; 4-B; 5-C; 6-B; 7-C; 8-B; 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
In today's Quick Queries video from Access Learning Zone, I'm here to tackle a variety of questions that frequently come up and don't necessarily need a full video on their own. While these videos are named "quick queries," they're actually designed to address various questions, not exclusively about database queries.
I recently met someone at an Access event who misunderstood the title, thinking these videos were purely about queries. Remember, it's a play on words, with "queries" also meaning questions. So if you've been avoiding them for that reason, I'd encourage you to check them out.
Today, we're starting with some questions and comments about my series on multiple cascading combo boxes. If you're not familiar, these are combo boxes where selecting an option affects the available options in subsequent combo boxes—like choosing a country, which then determines the states available, which in turn affects the counties, and so forth. We set up five of these, though you can have as many as you need.
Jade Dragon mentioned that regions in Ontario are organized like those in British Columbia and pointed out how terminology can vary, like parishes in Louisiana or the numerous counties in Texas. You could certainly create additional tables or include regional terminology in your existing tables, updating captions accordingly. If enough people are interested, I'm open to making a video on how to do this.
Gregory asked why we store information like city, county, and state in customer records when a street ID could provide that relationally. He's correct—holding just the street ID could yield the related data. However, I use this approach to accommodate scenarios where only partial information is available, such as only knowing a customer's state. It's more about practicality and dealing with incomplete data, even if it's not fully normalized. If you prefer building your database the other way, go ahead.
Cyberman posed a question about handling cases where someone might enter a state or city without specifying a country. Currently, our setup requires a country selection to filter states. However, you could implement a wildcard option to display states from all countries or leave fields unfiltered initially. There are various configurations you could use, and if there's enough interest, I'm willing to create a video on this topic as well.
Bullenti asked about scenarios where some countries don't use states or counties, applying the same approach to organizational levels like sectors or teams. The straightforward solution is to add an "all" option for countries without states. For more advanced handling, you could incorporate logic with VBA. It's flexible, and I could explore this further if there's enough demand.
Rick L inquired about lessons on the "not in list" events that prompt a form when a new item is added and update the list box accordingly. While I touch on this in the extended run of my relational combo boxes video, the more complex steps are covered in Access Developer 35. This involves prompting users to add new data and involves some VBA scripting.
George asked about using classes in Access and whether they could streamline our setup. While I haven't covered classes yet in my Developer course, mainly because I haven't found them necessary, there's interest, and I'll address them in the future. Although classes are useful in object-oriented programming, I haven't found them indispensable in Access.
Mitchell wanted to know how to correct errors like placing a U.S. state in the wrong country. Editing can be done directly in the edit form or table. Building a form with parent combo boxes may also facilitate this task. Reassessing user permissions to make changes directly is viable, but it may not always be advisable.
I've also received some feedback on an April Fools' joke, which was enjoyed by some. And yes, there's merchandise like mugs available, though it's not something I've actively promoted.
That wraps up today's quick queries. Thanks for joining, and I hope you found this session helpful. For more detailed video tutorials with step-by-step instructions, visit my website at the link below. Live long and prosper, my friends.
Topic List
Multiple cascading combo boxes
Handling different regional naming conventions
Redundant information storage in databases
Partial information in databases
Using unbound boxes for non-storable data
Wild card options for filtering data
Programming exceptions for missing levels
"Not in List" event handling
Class utilization in Access forms (discussion)
Fixing or editing incorrect data entries
|
