Sunday, May 03, 2009

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!