Filter Combobox based on Another Combobox

How to synchronize two combo boxes in a Microsoft Access Form.


Watch the video and/or follow the step by step instructions below (this will take about 15 minutes of your time but you will get what you came for!)






Requirements

• This example uses MS Access 2007 but it should also work in previous versions.
  
• You will need six objects in your database:




• One form: frmMain
  
• Two combo boxes: The Master combo box: cboProjects and The Slave combo box: cboProjectTasks
  
• One table: tblProjectsAndTasks
  
• Two queries: The Master query: qryProjects and The Slave query: qryProjectTasks

STEP 1: Create the Form:

• Create a new form and name it frmMain.
  
• Save the form.

STEP 2: Create the combo boxes:

• Create the Master combo box.
  
• Name it: cboProjects
  
• Create the Slave combo box.
  
• Name it: cboProjectTasks
  
• Save the form.

STEP 3: Create the data table:

• Create a table and name it: tblProjectsAndTasks
  
• Add these FieldNames (and DataTypes):




• TaskID (Autonumber)
  
• ProjectName (Text)
  
• TaskName (Text)


• Populate the table.




• In this example the projects are: Alpha, Delta and Gamma.
  
• And each project has multiple tasks, for example:




• Project Alpha Task 1
  
• Project Alpha Task 2
  
• Project Gamma Task 3

STEP 4: Create the master query:

• The Master Query should be named: qryProjects
  
• This query will select unique project names from tblProjectsAndTasks.
  
• It will be the data source for the master combo box.
  
• The SQL code for this query is: SELECT DISTINCT ProjectName FROM tblProjectsAndTasks;
  
• Alternatively, you could use the following SQL code: SELECT ProjectName FROM tblProjectsAndTasks GROUP BY ProjectName;
  
• Either way, the output should be:




• Project Alpha
  
• Project Delta
  
• Project Gamma

STEP 5: Create the Slave Query:

• Create a new query and name it: qryProjectTasks
  
• This query will show tasks related to the project that is selected in cboProjects.
  
• The SQL code for this query is: SELECT DISTINCT ProjectName, TaskName FROM tblProjectsAndTasks WHERE ProjectName =[forms]![frmMain].[cboProjects].[value];
  
• At this point, running this query will not return any results because the WHERE clause is looking for the project selected in cboProjects, which is not data bound yet!

STEP 6: Data Binding the Master Combo Box:

• Give cboProjects these properties:




• Row Source: qryProjects
  
• Row Source Type: Table/Query
  
• Column Count: 1
  
• Bound Column: 1

STEP 7: Data Binding the Slave Combo Box:

• Give cboProjectTasks these properties:




• Row Source: qryProjectTasks
  
• Row Source Type: Table/Query
  
• Column Count: 2
  
• Column Width: 0";1"
  
• Bound Column: 2

STEP 8: Refreshing the Slave Combo Box:

• The slave combo box needs to be refreshed after each change in the master combo box. The VBA procedure below will refresh the slave combo box.
  
• This procedure should be tied to the On Change event of the master combo box. Private Sub cboProjects_Change() Me.cboProjectTasks.Requery End Sub

STEP 9: Clearing the Slave Combo Box:

• Refreshing the combo box does not clear the current task selected.

Modify the VBA procedure to clear the combo box:

Private Sub cboProjects_Change()

Me.cboProjectTasks.Requery

Me.cboProjectTasks = ""

End Sub


STEP 10: ENJOY!