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

usmcgrunt · #1 (**permalink**) 08-24-10, 20:33

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

Sinndho · #2 (**permalink**) 08-25-10, 03:29

You could dynamically modify the RowSource property of the combo in the subform, according to the selection of the combo cboDivSelect.

usmcgrunt · #3 (**permalink**) 08-25-10, 06:41

Quote:

Originally Posted by Sinndho

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

Sinndho · #4 (**permalink**) 08-25-10, 07:23

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

usmcgrunt · #5 (**permalink**) 08-25-10, 10:30

Quote:

Originally Posted by Sinndho

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.

usmcgrunt · #6 (**permalink**) 08-25-10, 21:04

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.

usmcgrunt · #7 (**permalink**) 08-26-10, 08:16

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

Internet Services

Web Development

Digital Marketing

Consumer Tech