Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2010
    Posts
    23

    Unanswered: Filter subForm comboBox based on main form combobox selection - With a TWIST

    Let me begin by saying thank you to those that choose to read and respond to this post. I have searched for quite some time to find a solution to this problem. I have found many suggestions but none seem to address my specific situation. So on to the details….

    Ok, here we go. I have a database that contains information pertaining to classes we teach at my college. I use college loosely because it is actually a military school that has the word college in its name. What I am trying to do is limit the 'lesson' choices available in a subform combo box to those that belong to a specific 'Division' that the user selects from a combo box in the main form. Here's the kicker. The lesson subform is made up of two fields. The first field is the combo box that will be filtered based on the main form 'Division' combo box. The second field is the description of the lesson. When a user selects one of the available 'lessonIDs', the associated description of the lesson will auto-populate. We have multiple 'divisions' with multiple 'classes' in each division. The 'lessons' are categorized by 'division'.

    Here are the tables and queries that I have already created that pertain to this part of the form.

    Table_Division
    DivName (PrimaryKey) - One

    Table_Lessons
    LessonID (PrimaryKey) - One
    DivName - Many
    LessonDescription

    These tables are linked by DivName with a one to many relationship.

    Table_DivData
    DivID (PrimaryKey) - One

    Table_DivLessons
    LessonID (PrimaryKey) - Many
    DivID (Foreign Key) - Many

    These tables are linked by DivID with a one to many relationship.
    Table_DivLessons is linked to Table_Lessons by LessonID with a many to one relationship.

    query_subformDivLessons
    This query contains all the fields from both the Table_Lessons and the Table_DivLessons.


    Form information

    Main Form = Form_LessonCard
    cboDivSelect [bound to Table_Division.DivName] This is the combo box that a user would first select and then all of the lessons available for that division would be available for selection in the Subform combo box.


    Subform = subForm_classLesson [Linked to main form by DivID] [data in this subform comes from query_subformDivLessons] [this form is in datasheet view]
    This is where I am lost. Right now I have two fields. The first field is a combo box that that lists all the 'LessonIDs' in the whole college. I want it to just list those lessons that pertain to the 'Division' selected in the main form combo box 'cboDivSelect'. The second field is the description of the lesson. It auto-populates when a its respect 'LessonID' is selected. If there is more than one lesson for a class, the user has the ability to select more LessonIDs.


    Hopefully this all makes sense.
    Again, thank you.
    Sean

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could dynamically modify the RowSource property of the combo in the subform, according to the selection of the combo cboDivSelect.
    Have a nice day!

  3. #3
    Join Date
    Aug 2010
    Posts
    23
    Quote Originally Posted by Sinndho View Post
    You could dynamically modify the RowSource property of the combo in the subform, according to the selection of the combo cboDivSelect.
    Sinndho, thany you for your reply. I researched your response but I must admit that I am by no means an access guru. If in fact a dynamic modification of the RowSource is an option what might the code look like?

    Thanks, Sean

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It could be something like this (though I'm not sure about the names of the objects on your forms):
    Code:
    Private Sub cboDivSelect_AfterUpdate()
    
        With Me.subForm_classLesson.Form.CboLessonIDs
            .RowSource = "SELECT LessonIDs FROM Table_DivLessons WHERE DivID = " & Me.cboDivSelect.Value
            '
            ' If DivID is not numeric use:
            ' .RowSource = "SELECT LessonIDs FROM Table_DivLessons WHERE DivID = '" & Me.cboDivSelect.Value & "'"
            '
            .Requery
        End With
    
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Aug 2010
    Posts
    23
    Quote Originally Posted by Sinndho View Post
    It could be something like this (though I'm not sure about the names of the objects on your forms):
    Code:
    Private Sub cboDivSelect_AfterUpdate()
    
        With Me.subForm_classLesson.Form.CboLessonIDs
            .RowSource = "SELECT LessonIDs FROM Table_DivLessons WHERE DivID = " & Me.cboDivSelect.Value
            '
            ' If DivID is not numeric use:
            ' .RowSource = "SELECT LessonIDs FROM Table_DivLessons WHERE DivID = '" & Me.cboDivSelect.Value & "'"
            '
            .Requery
        End With
    
    End Sub


    Thanks for the clarification; I will give it a go and report back.

  6. #6
    Join Date
    Aug 2010
    Posts
    23
    Sinndho, Ok, so I used the code you provided above with no luck. I am beginning to think that it has more to do with the tables and queries I have that relate to the form and less to do with the form itself.

    So as a generic question. How would I go about filtering a subform combobox after first selecting a choice from a main form combo box. Again, the kicker here is that once I select a choice from the subform combobox the adjacent field in the subform combobox autopopulates.

    The steps I describe below take place when I add a new class to the database.

    When I open the form up the first thing I do is type the new class into a textbox called txtClass.

    Next, I select a division from a mainform combobox called cboDivisionSelect. There are 5 choices available.

    Next, I select a lesson from the subform combobox called cboLessonSelect. Right now all 23 lessons in the school appear when I click on the combobox. What I am trying to do is only have those lessons that are associated with the division I selected in the mainform combobox cboDivisionSelect showup. Once I select a lessonID the adjacent field in the subform auto-populates with the lesson description. Then I can select more lessons if the class has more. The subform is in datasheet view.

    I hope this provides more clarity. I am trying to build a miniature version of my database to upload.

    Thanks, Sean.

  7. #7
    Join Date
    Aug 2010
    Posts
    23

    Closed Topic

    Consider this topic closed; I will repost in a new topic with more succinct details. Thanks Sinndho.

Tags for this Thread

Posting Permissions

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