[AccessD] Filtering Passthrough Query for Subreport

[David Emerson]

Thanks Bill, but I think you have highlighted my problem - where do I update
the recordsource so that the correct records are filtered?

My examples below tried to apply the filters in the properties.  Perhaps I
should be trying it in code but I can't find a place where it works.  I have
tried putting this in the Detail_Format event of the srpClientDetailClaim
subreport but I get an error message (below):

 
[Reports]![rptClientDetailHeld].[Report]![srpClientDetailClaim].[Report].srp
ClientDetailClaimPackage.Report.Requery

Error 2467 message: The expression you entered refers to an object that is
closed or doesn't exist.

I have put msgbox
[Reports]![rptClientDetailHeld].[Report]![srpClientDetailClaim].[Report].srp
ClientDetailClaimPackage.Report.Name after the report is opened and I get
the report name so I know the reference is right but not in the right place.

Any further help anyone?

Regards

David

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

In general, I set a querydef variable to the base query, assign the SQL
property to a string, and parse and otherwise manipulate that string to
re-develop the needed SQL statement to become the necessary recordsource. I
have not tried (recently) this in respect of a pass-through query but I
can't think of any immediate reason this would not work.

Naturally if doing this you will want a different temporary query so
constructed for every report and subreport because otherwise you cannot open
reports up side by side (ie, if you were to change the actual query that
might be relied upon by more than one report or subreport, then this will
confuse Access).

Mileage may vary....

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].[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