Access - Linked forms

I have a situation whereby I need 3 linked forms. This has arisen because of this scenario:
A vehicle will be sent out to several locations, and at each location it will pick up several items. So I need to record:

vehicle details
date of collection

Customer Site

Collection Items
Item collected

So there will be one or many Collections per Load, and one or many Collection Items per Collection.

What is the best way to enter and display these details? Normally I would contemplate using subforms, but with 3 levels this could become unwieldy. Does anybody have ideas or examples of a good solution please
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.

How about instead of 'nested' subforms, having side-by side related subforms for the Collections and Collection Items:

Main Form:  Load
Subform 1: Collection (linked to the main Load form)
Subform 2: Collection Items (Linked to the Collection subform, but placed next to or underneath rather than nested in the collection subform)

Code in the Current Event of the Collection subform would be used to control the filtering of the collection Items subform, so that when the user navigates between records in the Collection subform, the Collection Items subform displays those items associated with the currently selected collection.

See the following question for details... the author has uploaded an image illustrating a sample layout, and the author's final post contains the code that makes this work:
Actually, here's a working sample - from the same question, with the solution that came out of the thread.

Try clicking different records in the Contract Years subform - and see how it controls the filtering of the other two subforms...

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:
I am happy to award full points for this - thanks fo rthe answer.

However, can I ask a cheeky extra question please? If there was a combo box in the 3rd level subform, and its value was dependent upon a value in the 2nd subform, how would I refer to this. And just to complicate matters, this whole form will be within a Navigation Form. If I need to ask this as an extra question, please let me know.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Me.parent.otherChild.form.textboxname for the combo box reference.

Can you post a sample db with no sensitive data to illustrate your layout?

I won't be at a computer to look at it for the rest of the day, so if you need more immediate help, go ahead and post a new question.
Fwiw, I don't think the navigation form will really complicate things much.
Rick DangerAuthor Commented:
OK - I'll implement the solution that you have provided, then post another question.

Thanks for your help, much appreciated!
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.