Apply a Filter to a Subreport

[Scott Duncan]

Hello,

I have a Global Variable, "MyFilterCriteria"

We have a report, 'Certificate of Calibration' that is opened with the
LinkCriteria of 'Test Number'
This Report has a Subreport, 'Test Weights'

We are already generating the Filter Criteria for the Subreport

We have tried both of these Statements on the On Open Event of the Subreport
with no Success:
Me.Report.Filter = MyFilterCriteria
DoCmd.ApplyFilter , MyFilterCriteria

It does not seem that the On Activate Event is fired when the report is used
as a Subreport...

Any ideas would be greatly appreciated !

TIA,

SD

 

[Allen Browne]

As you found, trying to set a Filter for a subreport is not generally
fruitful.

An alternative is to base the subreport on a query that reads the values
from the form. For example, you might put something like this into the
Criteria row of your query under the Weight field:
Between [Forms].[Form1].[MinWeight] And [Forms].[Form1].[MaxWeight]

If you are desperate, and the report is opened by clicking a button on a
form, another option is to reassign the SQL property of the query that is
the RecordSource for the subreport before opening the report:
dbEngine(0)(0).QueryDefs("Query1").SQL = "SELECT ...

 

[Scott Duncan]

Allen,

The reassign the SQL property of the query works like a champ.

Thank you !

SD

As you found, trying to set a Filter for a subreport is not generally
fruitful.

An alternative is to base the subreport on a query that reads the values
from the form. For example, you might put something like this into the
Criteria row of your query under the Weight field:
Between [Forms].[Form1].[MinWeight] And [Forms].[Form1].[MaxWeight]

If you are desperate, and the report is opened by clicking a button on a
form, another option is to reassign the SQL property of the query that is
the RecordSource for the subreport before opening the report:
dbEngine(0)(0).QueryDefs("Query1").SQL = "SELECT ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I have a Global Variable, "MyFilterCriteria"

We have a report, 'Certificate of Calibration' that is opened with the
LinkCriteria of 'Test Number'
This Report has a Subreport, 'Test Weights'

We are already generating the Filter Criteria for the Subreport

We have tried both of these Statements on the On Open Event of the
Subreport with no Success:
Me.Report.Filter = MyFilterCriteria
DoCmd.ApplyFilter , MyFilterCriteria

It does not seem that the On Activate Event is fired when the report is
used as a Subreport...

Any ideas would be greatly appreciated !