2nd and 3rd Sub-Sub Form Not Working

BBlu
BBlu used Ask the Experts™
on
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.
 Relationships
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
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.

Author

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
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
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..  
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Nerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
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).

Author

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.
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
    Else
        On Error GoTo Form_Current_Err
       
        Me.Parent![S_RevSharePmts].Requery
        Me.Parent![S_Guarantees].Requery
       
        
    End If

Form_Current_Exit:
    Exit Sub

Form_Current_Err:
    MsgBox Error$
    Resume Form_Current_Exit

End Sub

Open in new window

Author

Commented:
Thanks for pointing me in the right direction.
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
<<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.

Author

Commented:
Thanks, again, mbizup.  

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial