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:
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:
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:
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.
Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016