Cascading Cbo Sub Datasheet

[andybuck86]

Hi all,

I'm having a tough time with my database.

I have the following tables:

tblCourses
tblCourseUnits
tblStudents
tblStudentsandCourses
tblStudentsandCourseUnits

I have a form called frmCoursesandStudents. This shows each record from the tblStudentsandCourses I.e. Students enrolled on courses

I have a subform (datasheet) on this form called frmStudentsandCourseUnitsSubform which shows student records for enrolled units on each course.

I can see the information I need perfectly.

My issue is when adding new units for each 'student and course record'. When clicking on my cboUnitID field I only want to be presented with Units that relate to that particular course.

I have the following code in already in my row source of cboUnitID

SELECT DISTINCTROW tblCourseUnits.CourseUnitID, [Title] & ", " &

 AS [Unit Title] FROM tblCourseUnits ORDER BY [Title] & ", " & [Code];[/QUOTE]

I need to add a condition to look at a field called [CourseID] on my main form and refer to my tblCourseUnits.CourseID to only show which units relate to that course

I hope this makes sense and would really really appreciate any help :banghead:

Here's what I tried. I think I'm pretty far off though! 

[QUOTE]SELECT DISTINCTROW tblCourseUnits.CourseUnitID, [Title] & ", " & [Code] AS [Unit Title], tblCourseUnits.CourseID FROM tblCoursesandStudents INNER JOIN tblCourseUnits ON tblCoursesandStudents.CourseID = tblCourseUnits.CourseID WHERE (((tblCourseUnits.CourseID)=[tblCoursesandStudents].[CourseID])) ORDER BY [Title] & ", " & [Code];[/QUOTE]

Thanks as always

 

[andybuck86]

Does nobody have any suggestions for this?

I would really appreciate any help

Thanks