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!)


video



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!

14 comments:

ian said...

Orlando,

this looks promising. I sent you an email about combo box sync of 3 boxes. i'am trying to sync 3 combo boxes, the second to the first, then the third dependign on the selection made in hte second. Will let you know how it goes.

Anonymous said...

Hello, Orlando!

Everything is fine except this:


SELECT DISTINCT tblProjects.ProjectName
FROM tblProjects;


Should be:


SELECT DISTINCT tblTasks.ProjectName
FROM tblTasks;

Anonymous said...

Wouldn't this mean that the value must depend on the combo box?

How would i go about doing that but having an option of showing all records?

Cheers.

Anonymous said...

Orlando,
This was awesome! It was the exactly the solutionat that I was looking for.
Thanks mr BaD @$$

Rich B said...

Orlando,
Thanks! Your instructions worked great. I have one question, however...I have several other controls on the form for other fields, and whenever I select an option from the drop down combo box during data entry, default values of zero immediate appear in all of the numeric field control boxes. How do I eliminate this from happening?

Thanks so much for your help!!!

Rich

Rich B said...

Orlando,
You can ignore my prebious question. I figured it out...I needed to make the change directly in the field properties of the table itself. I deleted the 0 default value which was listed.

Thanks anyway!

Rich

Anonymous said...

Although, your example had some confusion, but this was the far most best example I have seen online for the Basing on another combobox. Your example helped solve my issue. Thank you again.

Anonymous said...

can someone help me with using 3 combo boxes? ive tried doing it using the same command as the second one.

pls help me on this

Anonymous said...

Thank you, Orlando! Have been ripping my hair out for 4 hours over this . . .

Anonymous said...

This is wonderful. Thank you very much!

Aaron Stauch said...

I was able to get this to work in a form, but I tried in a subform it stoped working. Could you please suggest a fix.

Thanks for getting me this far

Anonymous said...

Much thanks. It works good.
-WEb GUy

Anonymous said...

Orlando,

Thanks a lot! It really helps unlike MS support!

Anonymous said...

I'M HAVING PROBLEMS WITH MY FORM WITH A SUBFORM.
I WANT TO SYNCHRONIZE A COMBO BOX FROM MY SUBFORM(TO LIMIT THE LISTS) BASED ON THE CHOICE ON THE ORIGINAL FORM'S COMBO BOX. CAN YOU HELP ME?