Inter-related subforms

I've already asked this question, and awarded points. However, I have since realised that it isn't quite what I requested. The attached solution assumes one master form with two children. What I am looking for is one parent form (S_ContractYears) with one child (Guarantees), and that has a child (S_RevSharePmts), which is a grand-child of  S_ContractYears. So S_ContractYears has one or many Guarantees, which in turn has one or many S_RevSharePmt.
Rick DangerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

At first you should change relationships in your database from:
Rick DangerAuthor Commented:
OK, I had actually already done that in preparation for the answer. I have removed T_Payments as it is not important for this exercise. It's more to do with how the subforms inter-relate.

Thanks though.
Can you upload corrected DB?
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Rick DangerAuthor Commented:
I've made a few changes to the subforms, which haven't really worked!
Rick DangerAuthor Commented:
Sorry, the upload didn't work. Here it is now.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You have a line of data that is 4 "levels" deep, so your forms need to reflect that.

First create a form based on T_RevSharePayments.

Next create a form based on T_Guarantees. Embed the "T_RevSharePayments" form created above and set the Master/Child links as needed.

Next create a form based on T_ContractYears. Embed the "T_Guarantees" form created above and set the Master/Child links as needed.

Finally create a form based on t_Verticals. Embed the "T_ContractYears" form created above and set the Master/Child links as needed.

If you set everything correctly, you should be able to scroll through your Verticals records, and the subforms (and sub-subform, and sub-sub-subform) will show the related data.
Rick DangerAuthor Commented:
Thanks, but I think the solution that you are suggesting is embedding subforms into their parents. The solution I am looking for is the one used in the uploaded example. So, although there is a hierarchy, I want them side by side, just like the example does. The example alomst does it, but the 3rd subform is a peer of the secnd subform, whereas I need it to be a child.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can do this:

1. Remove the "Default value" expression from the guaranteeID textbox on the s_RevSharePmts subform. You should allow Access to handle that relationship.
2. Name the subform for the s_Guarantees table to "sfS_Guarantees"
3. Change the Master link field for the S_RevSharePmts subform to this:


REvShareID is the name of the Textbox on the S_Guarantees form you're using as a subform ...

You also don't need to requery the subforms, as you're doing in several Current events. Let Access handle that for you.
Rick DangerAuthor Commented:
It doesn't sem to work. If I create 2 instances of Contract Years, I would expect that by selecting either of these would lead to different Guarantees being displayed. So I have created 2 different Contract Years, and the first of these has 2 Guarantees, the second has none. When I select the first, I expect the 2 Guarantees to appear, and when I select the 2nd, I expect them to disappear, but they are still there.
Rick DangerAuthor Commented:
I more or less took out everything you suggested, and put in a few bits and pieces, and it now seems to work fine. Have I missed the point about something, and can somebody tell me whether the approach I have adopted is the most sensible / efficient. This is just an example of something I need to do, so I want to make sure I have got it right.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Personally, I still think you're better off using the embedded approach I suggested, even if it means modifying your layout a bit.

That said - you should not set the Default Value of your guaranteeID field. Let Access manage that, otherwise you'll end up with orphaned records.
Rick DangerAuthor Commented:
OK, that's personal choice - I respect your views.

With regard to your second point - I tried that, but it did not populate the guaranteeID field unless I set the default.

Thanks for your help though.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I tried that, but it did not populate the guaranteeID field unless I set the default.
It does for me, and it should - that's default Access behavior. If yours does not do that, I'd be leery of corruption or faulty Access install.

I removed your Default Value from S_RevSharePmts.GuaranteeID, and set the link fields to this:

Master: sfS_Guarantees.Form!GuaranteeID
Child: GuaranteeID

When I did this, I was able to cycle through the records, and could add new RevSharePmt records and such

Simply put, you should never have to force a bound subform's "link" values. You should set the link fields and let Access do the work. If you are having to set those values on your bound form, then something is amiss in your database, or your environment.

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
Rick DangerAuthor Commented:
Would you mind uploading a copy of that please? Maybe I'm doing something wrong, so it'd be nice to see another solution.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Sure - but as I said, all I did was remove the default from that field.
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.