Complicated Recordsource problem

[Stewart Allen]

I have a form with 2 subforms; 1 displaying the modules a trainee is
currently doing and the second subform displaying the units that are
associated with the selected module while the main form is linked to a
Student. If the user selects module "1.04" the second subform should display
4 units which make up that module; 1234, 1235, 1236 & 1287.

This I can do. What I want to do is also display a tick in a check box if
the student has passed a unit for that module. e.g passed 1234 & 1236 for
module "1.04".

1) One Module can contain many Units and one Unit can belong to more than
one Module.
2) The StudentModules and the StudentUnits are not linked together directly
but only via the StudentID of the Students table.
3) A Student decides on which Module (s)he study and this is entered into
StudentModules.
4) When a Student starts a Unit, this is when the Unit is entered into the
StudentUnits table.

Below are the table structures, all foreign keys combine into a composite
key.

*Modules*
ModuleID (PK)
ModuleNumber
ModuleDescripti on

*Units*
UnitID (PK)
UnitTitle
UnitDescription

*ModuleUnits*
ModuleID (FK) from Modules
UnitID (FK) from Units

*Students*
StudentID (PK)
FName
LName
etc

*StudentModules *
StudentID (FK) from Students
ModuleID (FK) from Modules
Required
Passed
DatePassed

*StudentUnits*
StudentID (FK) from Students
UnitID (FK) from Units
Passed
AssessmentDate

How can I make a recordsource for the second subform which displays all the
Units for the selected Module and indicates which Unit(s) the Student has
passed.
Module "1.04" selected in subform 1.
Subform 2 displays
1234 (tick)
1235 (no tick)
1236 (tick)
1287 (no tick)

TIA
Stewart

[James Fortune]

Re: Complicated Recordsource problem

"Stewart Allen" <sagasu@NOT.wav e.THIS.co.nz> wrote in message news:<cdg81f$d2 b$1@news.wave.c o.nz>...[color=blue]
> I have a form with 2 subforms; 1 displaying the modules a trainee is
> currently doing and the second subform displaying the units that are
> associated with the selected module while the main form is linked to a
> Student. If the user selects module "1.04" the second subform should display
> 4 units which make up that module; 1234, 1235, 1236 & 1287.
>
> This I can do. What I want to do is also display a tick in a check box if
> the student has passed a unit for that module. e.g passed 1234 & 1236 for
> module "1.04".
>
> 1) One Module can contain many Units and one Unit can belong to more than
> one Module.
> 2) The StudentModules and the StudentUnits are not linked together directly
> but only via the StudentID of the Students table.
> 3) A Student decides on which Module (s)he study and this is entered into
> StudentModules.
> 4) When a Student starts a Unit, this is when the Unit is entered into the
> StudentUnits table.
>
> Below are the table structures, all foreign keys combine into a composite
> key.
>
> *Modules*
> ModuleID (PK)
> ModuleNumber
> ModuleDescripti on
>
> *Units*
> UnitID (PK)
> UnitTitle
> UnitDescription
>
> *ModuleUnits*
> ModuleID (FK) from Modules
> UnitID (FK) from Units
>
> *Students*
> StudentID (PK)
> FName
> LName
> etc
>
> *StudentModules *
> StudentID (FK) from Students
> ModuleID (FK) from Modules
> Required
> Passed
> DatePassed
>
> *StudentUnits*
> StudentID (FK) from Students
> UnitID (FK) from Units
> Passed
> AssessmentDate
>
> How can I make a recordsource for the second subform which displays all the
> Units for the selected Module and indicates which Unit(s) the Student has
> passed.
> Module "1.04" selected in subform 1.
> Subform 2 displays
> 1234 (tick)
> 1235 (no tick)
> 1236 (tick)
> 1287 (no tick)
>
> TIA
> Stewart[/color]

How about something like:

Select UnitID, Passed FROM StudentUnits WHERE StudentUnits.Un itID IN
(SELECT ModuleUnits.Uni tID FROM Modules INNER JOIN ModuleUnits ON
Modules.ModuleI D = ModuleUnits.Mod uleID WHERE Modules.ModuleI D =
Forms!frmMain.F orm!SubformModu les!ModuleID);

James A. Fortune