Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004

    Unanswered: Subcategories available when categories are Selected

    I have been trying to figure out how to have subcategories available in a Combo Box when a certain category is selected. Then each subcategory has its own subcategory that will also need to be available. Any help would be appreciated.

    Last edited by Ellsworth; 12-11-04 at 19:09.

  2. #2
    Join Date
    Jul 2003
    Here's what you need to do. I'll call your first category "Major", and your second category "Sub"

    First create 3 tables:
    1 with all your major category names, and an autoindex field (integer)

    1 with all your sub category names and an autoindex field

    The third table will have 2 fields to link your majors and subs.
    Column 1 contains the major id number, and column 2 contains the sub category id number. Your data would look something like this:

    1 1
    1 3
    2 1
    2 4
    3 2
    3 3
    3 5
    (I call this table category_id in my example below)

    Now on your combo box for the Major category, in the afterupdate event put code like this:

    Private Sub cmbMajor_AfterUpdate()
    Dim strSql As String
    strSql = "SELECT sub_categories.SubCatNum, sub_categories.SubCatName, category_ID.MajCatNum FROM sub_categories INNER JOIN category_ID ON sub_categories.SubCatNum = category_ID.SubCatNum WHERE (((category_ID.MajCatNum)=" & cmbMajor & "));"
    cmbSubs.RowSource = strSql
    cmbSubs = Null
    End Sub

    This will set the rowsource property on the sub category combo box to only valid sub related to the selected major.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Dec 2004
    Works great, thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts