[AccessD] Filtering Passthrough Query for Subreport

[David Emerson]

Hi Bill,

I even tried saving the data as tables and using them as the source of the
sub reports but couldn't get them to work (not sure why because I have not
had problems will Access tables and queries.  In the end I combined the two
subreports into one query and used grouping to achieve the same result.

Pity I couldn't nut this one out but at least I have a working solution.

Thanks for your efforts.

Regards

David

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Bill Benson
Sent: Sunday, 18 October 2015 12:06 a.m.
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Filtering Passthrough Query for Subreport

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].[repor
> t]![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].[repor
> t]![tx
> tClaimID]
>
> Any other hints to help me solve this?
>
> Regards
>
> David Emerson
> Dalyn Software Ltd
> Wellington, New Zealand
Website: http://www.databaseadvisors.com