[AccessD] Filtering Passthrough Query for Subreport

[David Emerson]

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