[AccessD] Filtering Passthrough Query for Subreport

David Emerson newsgrps at dalyn.co.nz
Fri Oct 16 15:26:22 CDT 2015


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



More information about the AccessD mailing list