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.


Record Source

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.


Control Source

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.


Row Source

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.


SELECT Category, CategoryID
FROM Item_Categories;


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()
Me.ItemID.RowSource = "SELECT Item, ItemID " & _
                      "FROM ITEMS " & _
                      "WHERE CategoryID = " & Me.CategoryID.Column(1)

Me.ItemID.Value = Me.ItemID.ItemData(0)
End Sub



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 populates.

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)
End Sub



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: support@moonlapsetech.com

HTML 5 Badge