2nd and 3rd Sub-Sub Form Not Working

I am new at Forms and am trying to show a Summary of Activity for Customer Accounts.  Each Customer Account can have several 1-year contract years.  For each Contract year, there is activity for a) Revenue Share; b) Payments Made; and c) Guarantees due

My relationships are set up with a Table for the Accounts (T_Verticals) with a one to many relationship to the contract years (T_ContractYears).  For each Contract Year, there are one to many relationships to the 3 tables mentioned above.

See attached for Relationships screenshot.
I've decided to setup a Form (T_Verticals) with a Subform (for the ContractYears) and additional subforms for the RevShares, Guarantees, and Payments.  

Using the Form Wizard, I was able to create the three levels (Form, Subform, Sub-SubForm) with only one (RevShare) of the Sub-Subforms.  

I tried copying the RevShare Sub(Sub)Form and doing the same for the 2nd of the the 3 Sub(Sub)Forms, but it is not working.  Specifically, when I change the contract year, The RevShare changes correctly.  However, the Guarantee records do not.  

Any ideas why?  Is it possible that this is disallowed?
 ContractID #1 ContractID #2
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

It sounds like this should work, provided that you have the Master-Child links set up properly.

From your relationship diagram, Revshare, Payments and Guarantees should all be subforms of Contract Years (they should all be on the same 'level')

The master-child links should between these forms and ContractYears should be on ContractID.  (They should be linked to ContractID, not to their 'sibling' forms).

Is that how you have this set up?

If possible post a sample database with junk data (no sensitive data) to illustrate this issue.
BBluAuthor Commented:

Thanks, mbizup.

Attached is the sample database.  Using the Form, "F_PPSAccounts", If you look at member/account 166466, you'll see what I'm talking about.

Thanks, again. Form-Parent-Child-Grandchild.mdb
It looks like you used a wizard to build your subforms ...

Can you describe how you added these subforms (and what version of Access tou used)?

The 'guarantees' subform, as you noticed simply doesnt seem linked to the contract years form, despite the master/child links..  
Maximize Customer Retention with Superior Service

The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.

If I had put this together manually, this would not be an issue... but at the layout would be completely different (the subforms would be embedded in each other).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BBluAuthor Commented:

Hey mbizup-

I used the wizard as follows:
1. I added fields I wanted-  Member_ID from T_Verticals; ContractID, StartDate, and DurationMo from T_ContractYears; and RevShareAmt and RevShareDate from T_RevSharePmts.  The Wizard then presented a hiearchy of relationships, and it was correct.  

The wizard wouldn't allow me to add a second GrandChild, probably because it didn't know how to handle it.  So I tried to add the second one (Guarantees) by copying the RevShare Subform and manipulating it.

I never thought about creating it from scratch, but I'll try.  Again, I'm new to Forms.
BBluAuthor Commented:
Okay, I think I got it.  You got me on the right track, mbizup.  The wizard created an update event that refreshed the subforms.  The code is attached.  Once I figured that out, I just mad sure it refreshed the additional Subforms (Grandchildren)

Creating it from scratch was totally helpful.  Thanks, again.

Sub Form_Current()

    Dim ParentDocName As String

    On Error Resume Next
    ParentDocName = Me.Parent.Name

    If Err <> 0 Then
        GoTo Form_Current_Exit
        On Error GoTo Form_Current_Err
    End If

    Exit Sub

    MsgBox Error$
    Resume Form_Current_Exit

End Sub

Open in new window

BBluAuthor Commented:
Thanks for pointing me in the right direction.
<<Again, I'm new to Forms. >>

I'm so glad you tried that, and even happier that you got it worked out (between midnight and 2:30 AM, my time noless :-)  ) !  That's the best way to learn this stuff, and I'm 100% convinced that you will find that creating subforms or any other controls from scratch gives you a lot more flexibility and control than the wizards offer.  The wizards suffice for many things, but being able to design your objects without them is a very useful skill.

That is a very neat solution, too - most 'grand-children' subform solutions involve a heirarchy of nested/embedded subforms (like I described earlier).  For datasheets, the solution you are using is a much nicer visual presentation.

BBluAuthor Commented:
Thanks, again, mbizup.  
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.