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

[usmcgrunt]

Update: Consider this topic closed. I will repost in a more succinct manner.

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