Comb Controls Refresh based on multiple tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I've looked through every message here and still have not found an answer to
the following situation. Please Help.

I have one form (frmClassDetails) that has 2 combo boxes with one dependent
on the choice of the other.

Combo box 1 is cboCourse and gets it's information from the cboCourse
rowsource SELECT query:
SELECT qryCourses.CourseID, qryCourses.CourseName FROM qryCourses;
Lets say the list of courses here is CT-000, CT-100, and CT-110.

The course chosen here needs to be used to help determine the list of
instructors to be displayed in the second Combo box cboInstructor. Currently
the second combo box just displays all the possible instructors based on the
following rowsource SELECT query:
SELECT qryTeachers.TeacherList, qryTeachers.VolStatus,
qryTeachers.VolunteerID FROM qryTeachers; This is from the teachers Table
tblTeachers.

I'm looking to include new fields in the tblTeachers to indicate which
teachers are certified to teach which courses. So lets say I add 3 yes/no
field types with names defined as CT000, CT100 and CT110 corresponding to
the possible courses.

What I have not been able to figure out is how to set up the rowsource
SELECT query in the cboTeachers combo box to list only the teachers qualified
to teach the course defined in combo box 1 (cboCourse).

Please help.
 
Search for the topic Cascading Combo Boxes for more information.

Basically, you'll need a third table that links teachers to courses (not just
more fields). The structure of this table will have the following at a
minimum: TeacherID, CourseID. Thus one course can be taught by more than one
teacher and one teacher can teach more than one course.

You will use the AfterUpdate event for the first combo box; actually, you can
do this from either direction (e.g. choose a teacher and then see what
courses are linked to the teacher). The AfterUpdate event will set the
second combo box's Row Source:

Me.ComboBox2.RowSource = "SELECT ... FROM ... WHERE Field='" & Me.ComboBox1 &
"';"
 
Hi Phil,

You need a junction table to resolve the many-to-many relationship between
tblteachers and tblcourses.

Try a google search as there are far better explanations already out there
than I could give.

CW
 
Thanks kingston and cheese_whiz, I'll follow up on the great suggestions and
reply with the final fix
 
Back
Top