Subform in Subform in Mainform

[farner]

I'm using Access 2007.

This is my setup:

Tables: Students, Parents, Tuition, Payments, Faculty

My database is for a school that involves Students, Faculty, Parents, Tuition, and Payments. What I have is the Students listed under their Parents (they register as families), and the Payments listed under Tuition (so that it shows the list of payments and dates).

Each set of parents has a Guardian ID, and Students are listed under their Parents by Guardian ID. Tuition also has Guardian ID, and payments should be listed under Tuition by Guardian ID.

The issue I'm having is when I register a family (input a Parents record and the related Students), I need, on the same form, for a Tuition record to be made so that when I input the payments (Date of Payment, Payment Amount), the Tuition Table will have the list of payments underneath a Tuition record with the same Guardian ID as the related Guardian ID in Parents.

The Tuition Table is supposed to be the same as the Parents table, except rather than show students underneath each Tuition record, it shows the payments (from the Payments Table).

I have a form that allows for the input of the parent information, with a subform in it for inputting student info, linked to the main form through the Guardian ID. That works fine. What I'm having an issue with is including a second subform, which allows for input of payments. I want the payments to have the same Guardian ID as the current Parent record, as well as for them to show up under Tuition, which also has the same Guardian ID as the parent record, in order for it to all be for the same family.

What I did at first was put a subform for payments (continuous) inside a subform for tuition, and then plugged that subform into the main form. I linked the Payments and Tuition forms with the Guardian ID, and the Tuition and Main form with Guardian ID. The first time I did it, it worked great, except it wouldn't allow me to create any new Parent records. As I was trying to fix that (I just needed to select the Cascade Update and Delete options in the relationships - but I didn't find that out till later), I tried to replicate what I had done the first time, but it didn't work. Now I can't do it at all anymore, and I lost the first file while playing around (I should have saved and backed up the file). Everytime I input info for the Parents, Students, and Payments, the Parents and Students comes out fine, but the Tuition and Payments have no Guardian ID and are not linked. I don't know what to do.

This is the first time I've ever used Access, and so my knowledge of things is minimal. I'll be grateful for any help.

[HansV]

Welcome to Eileen's Lounge!

You mention that "The Tuition Table is supposed to be the same as the Parents table", with Guardian ID as key. It seems to me that you don't need a separate Tuition table. Payments could be directly underneath the Parents table, linked by Guardian ID. That would simplify the form too: you could have a Payments subform on the Parents main form, with Guardian ID as Link Master Fields/Link Child Fields.

Or am I missing a detail?

[farner]

It is supposed to be the same, except if I put both Payments and Students under Parents, the Parents table only allows me to see either or, not both. I'm trying to get it so that I have a Parents table that shows me the students, and another that shows me the payments.

[HansV]

You shouldn't try to see everything in the table. Use a form for that purpose.
You can have two subforms on one main form, next to each other, or in two different tabs of a tab control.

[farner]

Hmmmm, if I did it that way and I wanted to see the list of payments, without needing to change it, would I be able to use a report to view it? Wouldn't the form just be used to input? Or could I see the list on the same form I input? I'm a noob so forgive me for my ignorance, and thank you.

[HansV]

If you want to view payments for all parents in one list, you'd use a report, although it would also be possible to create a form for that purpose.
Similarly, if you want to view all students in one list, you'd generally use a report, although again it would be possible to create a form for it.
The parents main form with students and payments subforms would only let you view the students and payments of one set of parents at a time.