- database solutions and downloads for microsoft access

Synchronize Microsoft Access Combo Boxes

How can I filter one Microsoft Access combobox based on another combobox selection?

The following article describes how to synchronize two Microsoft Access combo boxes so that when you select an item in the first combo box, the selection limits the choices in the second combo box.

Assume you have the following two Microsoft Access tables:


  • lngStoreID (Primary Key)
  • strStoreName
lngStoreID strStoreName
1 M&S
2 Binns
3 Safeway
4 B&Q


  • lngManagerID (Primary Key)
  • lngStoreID (Foreign Key - tblStore)
  • strManagerName
lngManagerID lngStoreID strManagerName
1 1 John Smith
2 1 Lee Thomas
3 1 Alison Jones
4 2 Tim O'Brian
5 2 Simon Marsh
6 3 Harry Hill
7 3 Sally Lees
8 4 Jenny Parker
9 4 Ian Jennings
10 4 Fred Lee
11 4 Bill Hardy
12 4 Alan Parker

You also have a Microsoft Access form with two comboboxes:

The form, containing the two combo boxes, that we will synchronize

Our combo boxes have the following Record Sources that provide their data:

RecordSource: SELECT [tblStore].[lngStoreID], [tblStore].[strStoreName] FROM tblStore;

RecordSource: SELECT [tblManager].[lngManagerID], [tblManager].[lngStoreID], [tblManager].[strManagerName] FROM tblManager;

You only want those managers visible that are in the store that has been selected from cboStore.

To do this you will need to modify the cboManager RecordSource in the AfterUpdate event of cboStore:

Private Sub cboStore_AfterUpdate()
    Dim sManagerSource As String

    sManagerSource = "SELECT [tblManager].[lngManagerID]," & _
                   " [tblManager].[lngStoreID]," & _
                   " [tblManager].[strManagerName] " & _
                     "FROM tblManager " & _
                     "WHERE [lngStoreID] = " & Me.cboStore.Value
    Me.cboManager.RowSource = sManagerSource
End Sub

You can apply this concept of having the value of one control affect the value of another by keeping in mind the AfterUpdate event of the first control is where you want to take the action on the second control.

Below, we see the form, with the second combo box un-filtered. You can see that we have not yet selected a value in the first (Store Name) combo box:

The second combo box, in an unfiltered state.

After selecting a value in the first (Store Name) combo box, we will see that the second (Store Managers) combo box is now filtered, and contains only values related to the selection in the first combo box. The two combo boxes are now synchronized.

The combo box, now synchronized with the first combo box.

To see an example of this in action please download the sample database from the Microsoft Access Forms page or Microsoft Access Downloads page.