[AccessD] Filtering Passthrough Query for Subreport

[Bill Benson]

Why no reference to Me?
On Oct 15, 2015 2:08 AM, "David Emerson" <newsgrps at dalyn.co.nz> wrote:

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