databasedev.co.uk - database solutions and downloads for microsoft access

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:

tblStore

• lngStoreID (Primary Key)
• strStoreName

tblStore

lngStoreID	strStoreName
1	M&S
2	Binns
3	Safeway
4	B&Q

tblManager

• lngManagerID (Primary Key)
• lngStoreID (Foreign Key - tblStore)
• strManagerName

tblManager

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:

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

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

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

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