[AccessD] Filtering Passthrough Query for Subreport
Jim Dettman
jimdettman at verizon.net
Sat Oct 17 07:33:22 CDT 2015
David,
Unless I'm missing something, it's sounds like your trying to over think
this.
Simply set the sub reports record source to the base query, then set the
master/child links as appropriate.
The sub reports will requery as the main report record changes.
Jim.
-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
David Emerson
Sent: Friday, October 16, 2015 08:01 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Filtering Passthrough Query for Subreport
All the events I tried came back with this error:
Error 2467 message: The expression you entered refers to an object that is
closed or doesn't exist.
Apparently it is because the sub subreport is created before the subreport.
I may need to save the data into a table and run the reports off tables
using main and child links.
Thanks for the help though.
Regards
David
-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Bill Benson
Sent: Saturday, 17 October 2015 10:59 a.m.
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Filtering Passthrough Query for Subreport
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].[Rep
> > or 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].[Rep
> > or 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].[r
> > > ep
> > > 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].[rep
> > or
> > 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
More information about the AccessD
mailing list