Excluding certain records - modification

[scottb]

Hi everyone,
This is related to my previous Post=184756 regarding restricting instructors available to teach classes. The requirement has changed that now some instructors can teach at one or many facilities, and are identified in tblInstructorFacility (which includes InstructorFacilityID, FacilityID, and InstructorID). The original SQL was:

SELECT ResourceID, ResourceName
FROM tblResources
WHERE ResourceID Not In (SELECT InstructorID
FROM tblClasses
WHERE InstructorID Is Not Null AND
ClassDate=[Forms]![frmClassAssignments]![ClassDate] AND
ClassStartTime<[Forms]![frmClassAssignments]![ClassEndTime] AND
ClassEndTime>[Forms]![frmClassAssignments]![ClassStartTime])
AND ResourceRole="Instructor" OR
tblResources.ResourceID=[Forms]![frmClassAssignments]![InstructorID];

I would like to be able to modify this to only show instructors where [Forms]![frmClassAssignments]![FacilityID]= tblInstructorFacility.FacilityID

Any help would be greatly appreciated. Thanks!

[HansV]

That would be

Code: Select all

SELECT tblResources.ResourceID, tblResources.ResourceName
FROM tblResources INNER JOIN tblInstructorFacility ON tblResources.ResourceID = tblInstructorFacility.InstructorID
WHERE tblResources.ResourceID Not In (SELECT InstructorID
    FROM tblClasses
    WHERE InstructorID Is Not Null AND 
        ClassDate=[Forms]![frmClassAssignments]![ClassDate] AND 
        ClassStartTime<[Forms]![frmClassAssignments]![ClassEndTime] AND 
        ClassEndTime>[Forms]![frmClassAssignments]![ClassStartTime]) AND 
tblResources.ResourceRole="Instructor" AND 
tblInstructorFacility.FacilityID=[Forms]![frmClassAssignments]![FacilityID] OR 
tblResources.ResourceID=[Forms]![frmClassAssignments]![InstructorID];

You should create an After Update event procedure for FacilityID to update the cmbInstructor combo box when a different facility is selected:

Code: Select all

Private Sub FacilityID_AfterUpdate()
    Me.cmbInstructor.Requery
End Sub

[scottb]

Hans,
It's working great. Thank you Hans!