[AccessD] Filtering Passthrough Query for Subreport

Fri Oct 16 16:58:36 CDT 2015

I'd be surprised if you could not find an event to suit.
On Oct 16, 2015 4:27 PM, "David Emerson" <newsgrps at dalyn.co.nz> wrote:

> I could try this but I don't think it will work because the source needs to
> change as the parent record changes - on Open may only fire up once when it
> is opened.
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Bill Benson
> Sent: Saturday, 17 October 2015 4:12 a.m.
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Filtering Passthrough Query for Subreport
>
> I would think you could use the Report Open event to modify the SQL of the
> recordsource, whether or not the recordsource is a passthrough. But I
> haven't done it, maybe you already tried that.
>
> On Thu, Oct 15, 2015 at 2:07 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].[Repor
> > t].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].[Repor
> > t].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].[rep
> > > or
> > > 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
> > >
> > >
> > >
> > > -----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
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>