[AccessD] Filtering Passthrough Query for Subreport

[Bill Benson]

David, I am going back to the beginning to try to understand better what
you are up against here. I think the problem here is that one cannot change
the recordsource of a subreport at runtime, the way you can with a subform.
A subform has a current event in which you can turn off, adjust, and turn
off the source object, change the sql of the sourceobject's underlying
object, reset the sourceobject, and requery the control's form property.
Reports and subreports don't operate that way.

I don't think this has to do with the passthrough aspect.

I think you have to redesign this as a client, claims, packages report, and
work with grouping and sorting to achieve your objective.



On Wed, Oct 14, 2015 at 4:31 PM, David Emerson <newsgrps at dalyn.co.nz> wrote:

> Hi Listers,
>
> Following up from my previous problem,  I have another report which
> displays
> information for a single client.   It has a subreport with several records
> for claims.  Each claim may have several packages so there is a subreport
> in
> the Claims subreport for packages.
>
> Just prior to opening the report I create the passthrough queries and use
> the client ID to get all package records for the client.  The package query
> is called qryTempQuery4.
>
> Then in the Package Subreport recordsource I have put the following:
> qryTempQuery4.  This returns all package records for all claims under each
> claim record (as would be expected).
>
> The query for the package subreport needs to be filtered based on the claim
> record. I am having problems with determining how I can filter the Package
> passthrough query with the claim ID for filtering the package records.
>
> I have tried putting the following in the Package Subreport recordsource
> property but this doesn't return any records at all:
> SELECT.* FROM qryTempQuery4 WHERE qryTempQuery4.ClaimID =
>
> [reports]![rptClientDetailHeld].[Report]![srpClientDetailClaim].[report]![tx
> tClaimID];
>
> I have tried putting qryTempQuery4 in the Package Subreport recordsource
> property and the following in the Filter property (still no records):
> qryTempQuery4.ClaimID =
>
> [reports]![rptClientDetailHeld].[Report]![srpClientDetailClaim].[report]![tx
> tClaimID]
>
> Any other hints to help me solve this?
>
> Regards
>
> David Emerson
> Dalyn Software Ltd
> Wellington, New Zealand
>
>
>