Form/Subform links.

[sahmad]

I would like to create a main form with two linked subforms as follows:

Students (Main)
Classes (subform1)
Assignments (subform2)

Where Classes is linked to Students and Assignments is linked to Classes.

The tables for the above are as follows
(where ----> indicates a one to many link)

Students---->Students/Classes<-----Classes

Classes----->Classes/Assignments<---Assignments

(That is to say that there are two many-many relationships between the three tables)

I am able to create a form which displays all Students and their related Classes, but when I insert the subform for Assignments, it will not show data related to the Classes. I have tried using a one-many relationship between Studnets and Classes, this works but not when I have a many-many relationship. What am I doing wrong?

Any help highly appreciated,
Thanks in advance.

 

[Pat Hartman]

Add the subform for the assignments to the classes subform rather than the student mainform.

This method will allow your form to show only one class at a time with a list of all the assignments for that class. You would then need to scroll through the class data to see all of the assignment data.

If you want to show a list of all classes and then when you click on one class show the assignments for that class, add a button to the class form that opens the assignment form.

 

[sahmad]

Thank you Pat for the suggestion, however two things: First although I insert the Assignments subform into the Classes subform I cannot see the related data. Should I create the Assignments subform as a query or stand alone form before inserting it and what should the Child and Master links be set as? Secondly I assume from your reponse and my initial difficulties that Access will not permit or is uncapable of inserting the two subforms into the main form so that they appear simultaneously like they do in the Northwind Customer Orders form. Is this assumption correct, since if it is I can give up endlessly trying to force it to do what it cannot?

Thanks in advance again

 

[Pat Hartman]

To answer your last question first, you can put both subforms on the main form but you'll have to control synchronizing the second sub form with the first sub form yourself. There is no built-in functionality that will do this for you. I believe there are examples of this in solutions.mdb or frmsmp97.mdb.

To get the nested subforms to work the parent child links must be properly set.
Students-to-classes should link on studentID
Classes-to-assignments should link on studentID and classID.

"Bridge players know all the tricks"