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:
You also have a Microsoft Access form with two comboboxes:
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 Me.cboManager.Requery 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:
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.