By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,317 Members | 1,297 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,317 IT Pros & Developers. It's quick & easy.

Complicated Recordsource problem

P: n/a
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
ModuleDescription

*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
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"Stewart Allen" <sa****@NOT.wave.THIS.co.nz> wrote in message news:<cd**********@news.wave.co.nz>...
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
ModuleDescription

*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


How about something like:

Select UnitID, Passed FROM StudentUnits WHERE StudentUnits.UnitID IN
(SELECT ModuleUnits.UnitID FROM Modules INNER JOIN ModuleUnits ON
Modules.ModuleID = ModuleUnits.ModuleID WHERE Modules.ModuleID =
Forms!frmMain.Form!SubformModules!ModuleID);

James A. Fortune
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.