I want to Link to drop down lists with each other in access 2007

Question

I'm trying to create a form that has several categories and sub categories.

Is there a way that i can create a relation between what is chosen in the first drop down (Main Category) to what is displayed in the second (sub category)?

I don't want to list all the sub categories at once. I only want the ones that relate to the specified one selected in the Main Category....

Thanks!

Answer 1

This is one of the most frequently-asked Access questions.

Approach 1: in the WHERE clause of the second combo box, use a reference to the first combo box as a criterion:

  PARAMETERS [Forms]![MyForm]![cmbCategories] Long;
  SELECT SubCategory
  FROM tblSubCategories
  WHERE CategoryID = [Forms]![MyForm]![cmbCategories]
  ORDER BY SubCategory;

(it's important to define the control reference as a parameter in versions of Access after 97 because those versions of Access don't handle Nulls in the same way as 97 and before did)

Then, in the AfterUpdate event of the first combo box, requery the second:

  Private Sub cmbCategories_AfterUpdate()
    Me!cmbSubCategories.Requery
  End Sub

Approach 2: Instead of hardwiring the second combo box's rowsource to include a reference to the first, write its rowsource on the fly in the first combo box's AfterUpdate:

  Private Sub cmbCategories_AfterUpdate()
    Dim strRowsource As String

    strRowsource = "SELECT SubCategory FROM tblSubCategories WHERE CategoryID = "
    strRowsource = strRowsource & Me!cmbCategories
    strRowsource = strRowsource & " ORDER BY SubCategory;"

    Me!cmbSubCategories.Rowsource = strRowsource
  End Sub

I tend to do the latter because I often need to do other things besides just filtering to the first combo box, but it's probably easier for most people to take the first approach.