Is this Possible: Main (tab / tab subform) filter

[tjones]

Basically i have 2 main forms StudentInformation and ActivityInformation (not sure if tabs are considered main/sub where the two fields are above the subdatasheet-subactivitystudentpage)

you select an activity to enroll a student in:
the subform on the studentinfo (tab Activity Enrollment) as you go through the students records it filters the information and the subform shows only those activities they have taken/enrolled in with (New) so you can enroll them in other activities

on ActivityInfo main form
the query pulling information for attendance subform is linked to the subform enrollment - when you scroll through the main activities the subform show you which students were enrolled in that particular activity

however i need to filter the choices available on the enrollment subform so that the directors do not need to scroll through all activity options, just their school and that session (8 schools - sessions Fall, Spring etc)

frmActivity
ActivityID
SchoolID
SessionID
+other related information fields

tblSchool
SchoolID
School

tbl Session
SessionID
SchoolID
Session

tblActivity
ActivityID
SessionID
Activity
+other fields

What I need the studentinformation/activity enrollment (tab/subform) to do:

when you select a participant and then the activity enrollment tab
1. the school and session fields are empty (and prefer unavailable session/activity)
2. the cursor goes to the school field
3. the school field selection filters the session field
4. the session field filters the selections available in activity

the datasheet Activity field pulls information from different tables and is based on a query (and fills a datasheet on another subform form linked to a different main form)

SELECT tblActivityEnrollment.StudentID, tblStudentInfo.LastNameStd, tblStudentInfo.FirstNameStd, tblActivityEnrollment.Activity, tblActivityEnrollment.SessionID, tblActivityEnrollment.WaitList, tblActivityInfo.ActivityID FROM tblActivityInfo INNER JOIN (tblStudentInfo INNER JOIN tblActivityEnrollment ON tblStudentInfo.[StudentID] = tblActivityEnrollment.[StudentID]) ON tblActivityInfo.ActivityID = tblActivityEnrollment.ActivityID;