Home  Fees/Services  Access Templates  Tutorials  Tutorial Downloads  Articles  Search  Contact  Privacy  Links

Cascading Combo Boxes
Access Combo Box


Updated On 1/16/2017 12:32:20 PM

Combo Box Tutorial






Combo Box Tutorials
Fill Form Field Code
Combo Box: All/Any
Dependent DropDown
Cascading Combo Box
ComboBox Multiple Items
Lookup List Best Way
Lookup Lists 1 Table
Faster Combo Boxes
Table-based Combos
Y/N Drop Down List


Dependent Drop Down List Box

For this dependent drop down list example let us assume that you only use certain shipping methods for orders being shipped to the New York versus New Jersey.
When the user enters information defining which state then we want to coordinate our combo box so it only shows methods pertaining to that state.
First we need to add another column to the L_Shipping_Methods table.  Now the table looks like this: 
Drop Down List Box examples

We must also make a change to the Row Source of the combo box.  The query design for the cascading combo boxes row source will now look like this:
 

 
Note that we have added the State field to the query design grid and have added a criteria which references our new State field on the form.

New! Download Access example of Dependent Combo Box

One last thing needs to be done.  MS Access isn't smart enough to know to look at the State_Choice field after it has changed.  So we need to tell Access to check it again whenever we change it.  To do this we add an After Update Event Procedure to the State_Choice field on the form.  The following is the visual basic code for the event:

One table for Look Up Lists

What this does is tell Microsoft Access to re-run the query for the combo box thereby getting a fresh look at the State field and synchronize the combo boxes.

Aside: note that I used the full reference to the form variable ( forms![form1]!  ).   Normally I would have used 'Me.' in place of the full reference.  However I have recently encountered a bug in Access 2002 where this can cause Access to completely crash.

And here is the end result of synchronizing the combo box:

cascading combo boxes

You can see that this technique can be used in many situations.  We could have used a combo box for the state field as well.   This technique works great on single record forms.  However, on continuous forms we have some additional work to do.  See that next method in a future update.

More Synchronized Dependent & Filtered Combo Box Examples:

Restrict Lookup Lists Based on Another Combo Box

Here we filter a lookup list/combo box based upon another dropdown list on the form.  Let us assume we are working with a form for students and we want to define a student's advisor for each student.  However,  student advisors only advise students in specific major fields.

So, based upon the student's major field a different list of potential advisors will be displayed in the combo box. The form, F_Students, has three fields:  Student_Name, Major_Combo, and Advisor_Combo. The following is the rowsource for the Advisor_Combo:

Select Advisor_ID, Advisor_Name
From L_Advisors
Where L_Advisors.Major_ID = Forms![F_Students]![Major_ID];

Note that the Major_Combo is made up of two fields: Major_ID (not visible) and Major.  Advisor_Combo also has two fields: Major_ID (not visible) and Major.  
Now this should work fine and it may appear to work on a single-record form or on the first record of a multi-record form.  However,  Access is stupid and will not automatically check to see if the Student_Major field has changed or if we have moved to a new record in a multi-record form.  This is an annoying oversight by Microsoft - although requering every combo or lookup list on a form on every new record could slowdown the form a lot. So, to get around this you need to do two things:

1) In the After Update event on the Major_Combo field of the F_Students form add the command:

Me.Advisor_Combo.Requery

2) In the On Current event of a multi-record
form add the same command as in 1) above.

More Resources for Dependent Combo Boxes:








A Blue Claw Database Design Article:

Learn How To Select A Consultant
 





Blue Claw Database Design Downloadable Tutorial:
Programming MS Access Security Alternative  





A Blue Claw Software Design Template:

Airline Reservations Database Template
 

Contact Information

Copyright 2000-2017 Blue Claw Database Design

Microsoft Access 2007, Access 2010, MS Access 2013