[AccessD] Filtering Passthrough Query for Subreport

[David Emerson]

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



-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
David Emerson
Sent: Thursday, 8 October 2015 3:27 p.m.
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Stored Procedure as Rowsource for Chart

Thanks Stuart - that was the missing piece.  Putting the name of the query
in the Rowsource property.

Much appreciated.

David

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Stuart McLachlan
Sent: Thursday, 8 October 2015 1:38 p.m.
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Stored Procedure as Rowsource for Chart

Don't use a temp query.  Used a stored query.

Create a Passthrough query as qryChartData which calls your SP with some
date.
Save that query.
Set that query as the row source.

Assuming that you open the report from a button click somewhere and you have
the desired date available then in your code modify and save the  qryDef.SQL
of qryChartData  before opening the report.



On 8 Oct 2015 at 13:24, David Emerson wrote:

> Thanks Stuart,  I looked at the article but I don't think it quite 
> fits my problem.
> 
> I already have code to get the results from a passthrough query.  My 
> problem is how and where I can set the Chart rowsource so that the 
> data from the stored procedure is used.
> 
> Here is the code (simplified) for the passthrough query:
> 
> Public grst1 As DAO.Recordset
> 
> Public Sub basReportPassThroughQuery(strSproc As String) ' Sample Call 
> (Report and 1 sub report) 'strSproc = "EXEC 
> dbo.spsrpClientQualitySurvey " & Nz(Me!txtID, 0) 'Call
> basReportPassThroughQuery(strSproc)
> 
>     Dim strTempQueryName As String, qdefTemp As DAO.QueryDef
> 
>     strTempQueryName = "qryTempQuery" 
>     CurrentDb.QueryDefs.Delete strTempQueryName 'In case exists
>     Set qdefTemp = CurrentDb.CreateQueryDef(strTempQueryName)
>     qdefTemp.ReturnsRecords = True
>     qdefTemp.ODBCTimeout = 120
>     qdefTemp.Connect = "ODBC;DRIVER=SQL 
> Server;SERVER=Servername;DATABASE=database;Trusted_Connection=Yes"
> 
>     qdefTemp.SQL = strSproc
> 
>     Set grst1 = qdefTemp.OpenRecordset
> 
> End Sub
> 
> Now that I have the recordset how do I get the chart to use it?
> 
> Regards
> 
> David
>