[AccessD] Filtering Passthrough Query for Subreport

[Bill Benson]

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