MS Access subreport filter based on another subreport

Question

I have 2 subreports called People_Documents and Documents_References on a main report People. 'People_Documents' has the normal parent/child relationship with People so only specific Documents appear based on the person in People.

What I would like is to have the same result for 'Documents_References' where the parent/child relationship would have only specific References appear based on the Documents that appear within the first subreport.

For example:

• People: John
• Documents: Document A, Document B, Document C
• References:
  • For Document A: Reference 1
  • For Document B: Reference 1 & Reference 2
  • For Document C: Reference 3

I know that subreports can only have parent/child relationships with the main report, so would it be possible to filter the 2nd subreport in some way based on what appears in the 1st subreport?

Any help would be very appreciated!

Answer 1

I would change the query subform Documents_References is based on to include the child field from the query which sits behind subform People_Documents. JOIN the base tables/queries in a way so that the child field is also available in that query. You can then set the same parent/child relationship form you have with People_Documents on Documents_References. From that point, whenever the record on the main form changes, both subforms should change with it.