Microsoft Access: How to Create Cascading Combo Boxes

in this lecture I want to show you probably what is one of the most popular questions that I get in regards to access and that is how to create a cascading combo box meaning when I choose an option from the first combo box or drop down I want the options for the second combo box or drop downs to only be related to the first one so if here's what I mean by that let me switch over to another form so here I have the different categories of everything that's sold in north wind so from the drop-down I can choose for instance cereal and from this drop-down I can see all the products under the cereal category let me show you another one if I go down to pasta here are all the pastas that are sold by Northwind if I go to baked goods here all the baked goods so this is what's commonly referred to as a cascading combo box and I'll show you how to create one it actually doesn't take that long at all so I have created a form called drop downs and right now there's actually not much at all in this form I just have two combo boxes and they don't have any information in them right now and as a matter of fact if I head over to my design view I can show you that the only thing that these two boxes have on them are names there's no control source right now there are no events associated with either of these two combo boxes so I'm basically starting from scratch here I just created a form and then add it in my combo box from the controls drop-down alright so here's what I did the first thing that I did was I had the top combo box point to all the categories that are in the products table and again I named this combo box category and I'm gonna switch over to data real quick and for row source I'll click on the build button and that's going to open up my query builder I'll bring show tables up a little bit and here I'm looking for the products table so I'll double click on products and then click on close so in the products table I'm going to choose category and I'll go ahead and run this and now taking a look at this I actually only have about 16 or 18 different categories but here I'm seeing 46 because it's bringing out each different line item remember there are multiple products in single categories so what I want to do here is go back to the design view make sure I'm looking at the property sheet for the entire query and the fastest way to do that is to just click once in the gray area here and that brings up the query property sheet and I want to make sure that I go to unique values and change that from no to yes all right now if I run this again I can see there are my unique 16 different categories and that's exactly what I want perfect so I'll head on back to design view and save this and close it alright so let me take a look at the form view real quick and category yep it's showing all my categories that's good so far next thing that I want to do is I want to be able to pick a category and then the products show up for that category so I'll switch back to design view and now I'll go over to my products drop-down or products combo box and again with that selected I want to go over to the property sheet I want to find row source and click on the build button one more time again I'll bring up show tables I want to again find the products table and double click on it go ahead and close this and I want to bring in both the product name and again if I scroll down I also want to bring in the category all right so this query right now would show me every product in every category but what I wanted to do here is I want to filter the product by the category so as far as category goes my criteria here will be whatever is selected in the form so only the products for the categories that are in the form will show up so under criteria I'll go ahead and type in forms and you can see here the forms collection pops up I can just double click on that now I want to tell access which form so I'm going to use my separator which is the bang or exclamation point and it is going to be the dropdowns form and then I want to tell it which fuel to look for so one more time my exclamation point and it is the categories field alright so if I expand this I want to see the product names but my criteria here is only for the categories that are showing up under category in the dropdowns form alright let me go ahead and save this and close it and there's one more thing that I have to do it actually works right now well kind of let me show you let me go to my form view and if I pick from here say condiments and click on the drop-down yep they're all my condiments but if I change this to say grains and click on the drop-down it's still showing my condiments so the last thing that I have to do here is to tell access that when I change the category to re-query this products drop down so I'll switch back over to my design view I'll click on the category drop down this time I want to go over to the event tab in my property sheet and I want to go to after update so after the dropdowns been updated with a new value I'll click on the build button on the right-hand side and I want to go to the code builder so I'll select that and click on OK alright so there's only one line of code that I need to add in here and that's the line that tells products to re query itself and that's products cuz that's again what I named that drop down products dot reek weary alright I'll go ahead and save that and close it I'll switch back over to my form view and I'll change it to say jams click on the drop down and there's my jams I'll switch it now to sauces click on the drop down and their sauces alright so this again is called a cascading combo box you make a change in one combo box click on the drop down and it makes a change in another

Channel: Software Success

Views: 63,731

Rating: 4.8463998 out of 5

Keywords: How to Create Cascading Combo Boxes, Access Drop Down Boxes, Access Database, Access 2016, Access 2013, Access 2010, How to Use Access, Udemy, Learn Access, Access Training, Database Training, Microsoft Access, Learn Microsoft Access, Database Tips, Microsoft Office, MS Access, MS Office, Combo Box, Database Tools, Udemy.com, Udemy Course, Database Drop Down, Dropdown, How to create a drop down box, Drop down list

Id: WFSvnT9LHn0

Length: 7min 9sec (429 seconds)

Published: Sat Jan 06 2018