Avatar of Rick Danger
Rick Danger
Flag for United Kingdom of Great Britain and Northern Ireland asked on

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
Microsoft Access

Avatar of undefined
Last Comment
Rick Danger

8/22/2022 - Mon

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:

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Rick Danger

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.

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck

Fwiw, I don't think the navigation form will really complicate things much.
Rick Danger

OK - I'll implement the solution that you have provided, then post another question.

Thanks for your help, much appreciated!