[AccessD] Filtering Passthrough Query for Subreport

Bill Benson bensonforums at gmail.com
Sun Oct 18 16:41:28 CDT 2015


I suspect that its due to the impossibility of changing a record source for
subports at runtime. You were bound to have to resort to grouping.

Good to have a solution!
On Oct 18, 2015 1:50 PM, "David Emerson" <newsgrps at dalyn.co.nz> wrote:

> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


More information about the AccessD mailing list