How to Make Cascading Combo Boxes in MS Access

i'm your host sean mckenzie thank you for joining me once again on my channel on data analytics and data engineering today by viewer request we are going to look at cascading combo boxes in microsoft access what are cascading combo boxes you might ask well drop down lists or combo boxes as they're called allow you to select information like a state or province or something like that and what a lot of people like to do is they like to have the ability to select one combo box and then have the result list in the second combo box get filtered by what was selected in the first one and so without further ado let's go take a look at our combo boxes okay so one of the first things i'm going to do here is i'm going to get some data and i'd like to credit geonames for this data and also thank luxman and the open knowledge foundation and what i did was i downloaded some documents i'll provide the link in the description i downloaded a data file and i'm just going to import it it's called world cities underscore csv and i'm just going to import it into access here and as you can see it's a delimited file it's got a whole ton of rows of different cities and regions and we can import this data and we can use it for our example and as you can see it's comma delimited file you can pretty much choose the defaults there and let access put a primary key on it which is always a good idea and this will save your uh your data to a file there were a few import errors but they were very minor and i think they're not central to our theme today so when we're done as you can see we've got a nice long list here of all the cities by country and their sort of province or state or region within each country that the city belongs to and this is going to give us a nice hierarchical data set that we can use to do our cascading combo boxes on and so the first thing we're going to do is we're going to start a new form so we chose to open in form design and then i'm going to go ahead and i'm going to choose a combo box from the toolbar up above that's in the design on the design ribbon and i'm going to cancel out of the wizard there we don't really need the wizard for this example and i can't type very well here there we go and uh so what i'm going to do first is create a combo box i'm going to put the label on it as country and then i'm going to copy that and i'll just paste the same one a couple of times here so you can see uh three combo boxes on there and uh got my third one down there and i'll put them in sort of order here i'll try to get them to look as neat as possible this isn't going to be a customer facing uh form so i think we're probably okay there but i'll try to straighten them up a little bit and then i'll change the label for the region and state as it is on the file that we created and i'll change the last one to be city so that we have a country which we want to select and then when we select that the region will be updated and then when we select that the city will be updated and so what i'll do is i'll go ahead and i'm going to name put names to our objects on there our combo boxes it's better to call it something useful than to call it combo 2. so we'll add a cbo region region state on there and we'll add a cbo city to our uh to our other one and we can go ahead and update that soon as it updates there that took a minute um and then i can add cbo city to that one and uh once i've got that label cbo city in there i'll go ahead and save our form and i'll just call it our city drill down and then we can move on to populating our our country drop down list okay and once we've got that in there then we'll click on our first combo box our cbo country which it hasn't updated in the top there i'm not sure why but we are in the the cbo country and what we're going to do is we'll go into the row source and we're going to grab the country from our list and we can select sql and in order to add a distinct keyword in there so that that's only going to give us the distinct names of the countries instead of having every row showing with duplicates in it and uh and then yeah we can go ahead and and save that um and close it and say oh i think i misspelled country on the end at the end there let's just go grab open that up once again and uh fix my mistake i called it entry instead of country there we go we'll grab country in our field list and then we'll close and save that and that's going to save it to our our row source and now we have a select distinct countries in our row source for our combo box and we can save that and then and then view it and we'll see if we drop use the combo box we've got our country in there that's what we wanted to see so we can find canada or united states or any country that you want to look for it's in our drop down box there so now what we want is we want to see our regions and states in the drop down list below after we choose hungary or canada or whatever in in the uh in the list above it and so in order to do that we're going to also add a row source to our second combo box there we'll do the same thing and we'll double click our cities and and get that onto our query builder and this time we'll select subcountry which is the name they used for regions and states and provinces and things like that and then we'll go ahead and grab the country field but we're going to not display it so we'll we'll uncheck show and then we're going to put a parameter in this query we're going to say forms exclamation city drill down which is our our form that we have so we're going to reference the value that's in the form and then we're going to grab cbo country and that's going to grab the value of the country so that whenever this query runs it uses the criteria of looking for what country was selected on our form so if we go back and we open the form we can grab our our drop-down list value but when we click the you know the second drop-down list we don't have any results and that's because the event that that populated the region state combo box happened already and there was nothing to look for and so what we're going to do is we're going to trigger that query once again and we're going to do that by using the after update event of the combo box uh the country combo box and so we'll use the ellipsis and we'll we'll choose to use uh the code editor and we'll do a simple little statement that says me which means the form is referencing itself and what that's going to mean is after the country combo box updates from the user selection we're going to tell the row source for the region state combo box to copy itself and basically update itself to itself and that forces a refresh and it's one of the best ways of always getting a good refresh when you're doing combo boxes there are other other ways to do it but that's the best way i've found after lots of experience so now if we update our country we can see that yes we have some values in our region state drop down but we've got lots of duplicates in there so we'll go back and we'll grab our row source for the for the regions and we'll just put a distinct in our select statement that's in the properties there and that's going to narrow it down to just the values individually with no duplicates and so now if i go ahead and choose another country i think i can even select the same one it'll fire that off and then we'll have a nice list of of all the different states in the united states and then if i go back to our design view and i click on our city drop down we're going to do the same thing we're going to look at our row source again click on the ellipsis to open a query for the row source we'll grab our cities table once again and we'll this time we'll grab the name of the city we'll give it another name because name is actually a reserved word and we don't like to use those for our for our column names so we'll call it cities and then just like the other one we're going to grab the level above it which is the subcountry uh we'll click off or uncheck the show button and then we'll put a parameter in to look for the value on the form of the region state and that means whenever this little combo box is refreshed it's going to look for a criteria of the subcountry or region and we'll also add the country as a parameter since it's possible that the same subcountry or region name could be in more than one country so this will really narrow it down to just our our region and country and give us our distinct list of cities and then we can go ahead and save save that we'll click on the the x there and it'll ask us if we want to update our row source with the sql statement that we've got there and we'll just go ahead this time and we'll put our distinct right into our list there so that we only get the values that we that we want with no duplicates so that it looks nice when we open it and run through the form and then we can take a look at uh adding our update statement to the region state box this time the after update event and we'll click on the ellipsis just like we did for the previous one and we'll choose the code builder and that's going to allow us to put a statement in there and we'll do the same thing that we did for the country drop down but this time we'll say that me cbo city dot row source equals me cdo city.rowsource and that's going to give us a nice update [Music] when the region state is changed and it'll refresh the list of cities to just the ones we want to see so if we run our form we can open it up and you see there's some old values in there from before we ran and we'll clean that up here in a minute um because new mexico is not in canada but if we chose canada then our list changed to just what we wanted and british columbia we'll choose that and then we can see our list of british columbian cities we can choose vancouver or whatever however it doesn't look very nice when we change the upper levels of the list and the values for the lower level are still there so in our hierarchy in our hierarchy and so um what we'll do is we're going to go clean that up and we'll go back to our after update statements for the two combo boxes above what we'll do is we'll say that the combo box get up gets updated with its row source like we just did and then we'll just set the value of the two other boxes to null when we use the country and we'll set just the value of the city combo box to know when the region state is updated and that'll make the user experience quite a bit nicer and i'd also note that you can play around with the dot enabled property of the combo boxes so that you can even lock them so people can't choose them and you know before there's a value in one of the upper higher level combo boxes so so as you can see now i can choose united kingdom and i'll get england look for london oh i got got the wrong one there look for lund london i can't type okay so uh we've got united kingdom and then if we choose canada it clears the boxes for us so that we don't have any leftover values and you can choose whatever you like there if we choose a different province you can see some other cities and if you choose yukon you can only get one city because it's such a small place and uh if you change the country to the united states you'll see everything gets cleared out we could choose california and then we could choose one of my favorite places which is san diego and that's how you do cascading combo boxes in access well i hope you enjoyed our discussion today on cascading combo boxes in microsoft access and if you like what you saw today please give it a thumbs up and subscribe to the channel if you haven't subscribed already and also click the bell when you click after you click subscribe so that you'll be notified of any new content that i put up on the channel also if you have any comments or suggestions or any special requests that you'd like to see on any of the topics that i've been covering from python to access to other data engineering techniques just leave it in the comments section below and i'll do my best to accommodate your requests a great day have a safe day and i'll catch you next time [Applause] [Music] so [Music] you

Channel: Sean MacKenzie Data Engineering

Views: 10,587

Rating: 4.9354839 out of 5

Keywords: How to Make Cascading Combo Boxes in MS Access, ms access combo, cascading combo boxes ms access, cascading drop down lists ms access, ms access filter combo box, ms access after update event, ms access update combo box, ms access rowsource, ms access row source, ms access combo box row source, country drop down list, city drop down list, ms access city combo box, ms access country state city drop down list, Sean macKenzie Data Analytics

Id: at0uaGSnUco

Length: 16min 7sec (967 seconds)

Published: Fri Jul 24 2020