Combo Box Synching with VBA
What is this trying to accomplish?
The goal is to have a a second combo boxes' possible selections
be limited by the slection in the first combo box.
For the example that this tutorial is covering. I am going to use a ITEMS
table for one of the selectable list and an ITEM_CATEGORIES table that will
contain a list of categories which will be used to limit the ITEMS list.
The ITEMS table has a CategoryID column that has contains the CategoryID
from the CATEGORIES table that is used to restrict the rows.
The third and last table that will be used is an ORDERS table. This table will contain
the ItemID from the ITEMS table and the CategoryID from the ITEM_CATEGORIES
table. Both of these fields are the auto number primary key's from those tables
so the columns in the order tables must hold numbers.
First, set the Record Source of the Order Form manually or create
the form off your desired table. This example is based of an ORDERS table.
Next, instead of using normal text fields for the CategoryID and ItemID; they have
been replaced with combo boxes. Then for each of these, make sure the Control Source
is set to each column and change the Bound Column setting to 2.
Next, since the CategoryID combo box is going to be the one that limits the ItemID
combo box the Row Source is going to be set with a SQL statement. Select inside the
Row Source option and then select the button with the dots that is off to the right.
That will bring up the query designer, switch it to the SQL View.
In the SQL View, the sql query needs to contain two columns in a specific order.
The first one should be
the column value that you want to be seen when being selected. The second column
is for the value that will be stored in the table that is being used used in the Record
Soure of the form. In this case it's the CategoryID field from the ORDERS table that is
storing the CategoryID field from the ITEM_CATEGORIES table.
Next with the CategoryID combo box selected. Select the After Update Event, click
on the button with the dots and select the code builder. Access will automatically
generate a procedure shell where the following code is entered. This VBA code is
triggered after the CategoryID combo box's selection has been updated; the code
sets the Row Source for the ItemID combo box with a SQL query that restricts
the rows to ones where the CategoryID column in the ITEMS table matches the
value that is in the second column in the CategoryID combo box. If
you notice that the line of code Me.CategoryID.Column(1) has a 1 instead of
a 2, that is becase the index in this case starts at 0.
Private Sub CategoryID_AfterUpdate()
"SELECT Item, ItemID " & _
"FROM ITEMS " & _
"WHERE CategoryID = " &
Lastly, some more VBA code needs to be added to the Forms' 'Current' event so that when the form is
opened on a record, the Item that has been selected for that records
Private Sub Form_Current()
Me.ItemID.RowSource = "SELECT Item, ItemID " & _
"FROM ITEMS " & _
"WHERE CategoryID = " & Me.CategoryID.Column(1)
Me.ItemID.Value = Me.ItemID.ItemData(0)
This method can be used to sync multiple boxes together, however, after two more code will need to
be written to handle boxes that are not matching up. Another benefit of synching the combo boxes
this way, is that say for example you decide to change the name of a selectable Item you would only
need to change the name in the ITEMS table and it will cascade througout the application because
the items are looked up by the ItemID number and not by matching text.
if any code is not working please email: email@example.com