Access Combo Box value depends on another Combo Box

This Access tutorial explains how to use Combo Box in Form where one Combo Box value depends on another Combo Box.

You may also want to read:

Access Form Combo Box and save selected value

Access Combo Box value depends on another Combo Box

In this tutorial, I will demonstrate how to use Combo Box in Access Form where one Combo Box value depends on another Combo Box using the below Login Form as an example.

The Login Form contains two combo boxes

– comboDivision: Contains a list of Division, this is the first Combo Box to select

– comboName: Contains Name (employee name), the value depends on selected Division

Step 1 – Set Row Source for Division (the first Combo Box)

Select comboDivision (the first Combo Box), and then enter into query builder in Row Source. (Row Source is the list of available values to be displayed in Combo Box)

Select Division Field from the Table in Query Builder.

In the above example, I use Group By to return distinct value from the Table. Alternatively, you can switch to SQL View in Query Builder and add keyword “DISTINCT” before Select. For example,

Select DISTINCT [Division] From …

Now you can see a list of Division value in comboDivision.

Step 2 – Set Row Source for Division (the second Combo Box)

Select comboDivision (the second Combo Box), and then enter into  Query Builder.

First, select Full Name, and then add one more field [Division], enter criteria

[forms]![Form1]![comboDivision]

Now you are using the value in comboDivision as a criteria for employee name.

Step 3 – Ensure first Combo Box value matches with second Combo Box value

Now so far so good, you select a Division and the corresponding Name displays for user to choose, but what happens if users reselect a Division? The Division gets updated but employee name remains unchanged, and it does not match with Division.

To address this issue, we need to insert the below code in the Change Event or AfterUpdate Event of ComboDivision

Private Sub ComboDivision_Change()
  ComboName.Value = Null
  ComboName.Requery
End Sub

The above code clear the employee name value when a ComboDivision changes, and then rerun the Query you built in ComboName.

The difference between Change Event and AfterUpdate Event is that Change Event is triggered even if users manually change a value, but AfterUpdate only triggers when combobox value is selected.

Outbound References

http://bytes.com/topic/access/answers/950683-combo-box-value-depends-another-combo-box