Cascading Combo Boxes Query

[didihynes]

Hi Guys,

I'm in desparate need of help. I am producing a database for my dissertation and have got majorly stuck. I am currently creating a form in which the user will select a student from a combo box, which that selection populates the next combo for the course selection. I have managed to do that with the coding shown below, my next combo will work from the course selection to bring back the feedback topics associated with that course, that there brings back one of my issues. Using the methods below i.e.queries, etc i cannot work out how to cross-reference one table to another using the results from the 2nd combo.

1st Combo Selection

Query

qryStudent

Select DISTINCT CoursesTakenbyS tudent.StudentI D
FROM CoursesTakenbyS tudent
ORDER BY CoursesTakenbyS tudent.StudentI D;

Row Source

SELECT DISTINCTROW [qryStudent].[StudentID]
FROM [qryStudent];

After Update

Private Sub cboStudent_Afte rUpdate()

Me!cboCourse = Null
Me!cboCourse.Re query

End Sub

2nd Combo Selection

Query

qryCourse

SELECT DISTINCT CoursesTakenbyS tudent.CourseCo de
FROM CoursesTakenbyS tudent
WHERE (((CoursesTaken byStudent.Stude ntID)=[Forms]![StudentFeedback]![cboStudent]))
ORDER BY CoursesTakenbyS tudent.CourseCo de;

Row Source

SELECT DISTINCTROW [qryCourse].[CourseCode]
FROM [qryCourse];

After Update

Private Sub cboCourse_After Update()

Here is the table structure i have in place:

tbl CourseTakenbySt udent
StudentID (pk)
CourseCode (pk)

These two tables are joined through a table Course via CourseCode.

tbl CourseFeedbackT opics
CourseCode (pk)
TitleID (pk)

If anyone could help it would be gratefully receieved.

Many Thanks

Didihynes

[Fiddler2]

Hi Guys,

I'm in desparate need of help. I am producing a database for my dissertation and have got majorly stuck. I am currently creating a form in which the user will select a student from a combo box, which that selection populates the next combo for the course selection. I have managed to do that with the coding shown below, my next combo will work from the course selection to bring back the feedback topics associated with that course, that there brings back one of my issues. Using the methods below i.e.queries, etc i cannot work out how to cross-reference one table to another using the results from the 2nd combo.

1st Combo Selection

Query

qryStudent

Select DISTINCT CoursesTakenbyS tudent.StudentI D
FROM CoursesTakenbyS tudent
ORDER BY CoursesTakenbyS tudent.StudentI D;

Row Source

SELECT DISTINCTROW [qryStudent].[StudentID]
FROM [qryStudent];

After Update

Private Sub cboStudent_Afte rUpdate()

Me!cboCourse = Null
Me!cboCourse.Re query

End Sub

2nd Combo Selection

Query

qryCourse

SELECT DISTINCT CoursesTakenbyS tudent.CourseCo de
FROM CoursesTakenbyS tudent
WHERE (((CoursesTaken byStudent.Stude ntID)=[Forms]![StudentFeedback]![cboStudent]))
ORDER BY CoursesTakenbyS tudent.CourseCo de;

Row Source

SELECT DISTINCTROW [qryCourse].[CourseCode]
FROM [qryCourse];

After Update

Private Sub cboCourse_After Update()

Here is the table structure i have in place:

tbl CourseTakenbySt udent
StudentID (pk)
CourseCode (pk)

These two tables are joined through a table Course via CourseCode.

tbl CourseFeedbackT opics
CourseCode (pk)
TitleID (pk)

If anyone could help it would be gratefully receieved.

Many Thanks

Didihynes

If I'm reading this correctly (and it is for a "dissertati on", not your homework) there is a logic flaw. For one thing, if your goal is to populate a combo box with available courses for the student to take, you would not be including the ones he/she already took, but would need to eliminate those.

You would be working with 3 tables: students, courses taken, and courses available.

Your first table would have studentID (and other student data)
Your "coursestak en" middle table would have studentID and courseID (and an assignmentID, but that's optional)
Your third table would have courses available id and course name

You would need an intermediary query to build this relationship for your second combo box. It would read something like this (put in your own field names):

SELECT coursestaken.st udid, coursesavail.co urseid, coursestaken.co urseid
FROM coursesavail LEFT JOIN coursestaken ON coursesavail.co urseid = coursestaken.co urseid
WHERE (((coursestaken .studid)=[Forms]![Form2]![cboStudents]))

now that we have the qryCoursesTaken , we need to eliminate those values from the second combo box by nesting that query in another query:

SELECT coursesavail.co urseid, coursesavail.CO URSE, qryCoursesTaken .studid, qryCoursesTaken .coursestaken.c ourseid
FROM coursesavail LEFT JOIN qryCoursesTaken ON coursesavail.co urseid = qryCoursesTaken .coursesavail.c ourseid
WHERE (((qryCoursesTa ken.coursestake n.courseid) Is Null))

Save this as qryCoursesAvail

Your recordsource for the second combo box would become this:

qryCoursesAvail

This is the way I see your code after properly setting up the tables and queries:

cboStudent.rows ource = Select distinct [studentid],[studentname] from qryStudent
cboCourses.rows ource = Select [courseid],[studentid] from qryCoursesAvail
afterupdate: me.Courses.requ ery