Setting Filter of SubReport

B

Bill Sturdevant

I have a report with a collection of Sub Reports. The Report does not have
an underlying query. The Sub Reports each have their own queries that pull
records. So there is no opportunity to do a Master-Child Link.

I have a form that allows me to pick options that I want to use to filter
each of the sub reports appropriately. As the options are picked, I assemble
appropriate filters in fields on the form.

If I physically copy the filters and paste them into the Filter on the Sub
Reports in design view and then open the report, everything works.

But, if I try to progarmmatically set the Filter of the subreports,
depending upon where I try to do it from , I get errors.

When I try to set the filter of the sub report in the Report_Open routine of
the Sub Report, I get the error "The setting you entered isn't valid for this
property".

I thought you were supposed to be able to set the Filter in this way. I am
probably missing something obvious.

How can I set the filter of the Sub Report programmatically.
 
A

Allen Browne

As you discovered, this does not work very well for reports.

An alternative approach is to set up the query of the subreport so that it
reads the values on the form. For example, if your form is named Form1, and
has text boxes named StartDate and EndDate, you could put this into the
Criteria row of your query, under the date field:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]
It is always a good idea to declare these parameters in your query
(Parameters on Query menu, in query design).

If that is not practical, another idea is to build the complete SQL
statement you want to the subreport to use, and assign it to the SQL
property of the QueryDef that feeds the subreport before the report is
opened. This kind of thing:
Dim strSql As String
strSql = "SELECT ... WHERE ...
dbEngine(0)(0).QueryDefs("MySubreportsQuery").SQL = strSql
DoCmd.OpenReport "MyMainReport", acViewPreview

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

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

message
news:[email protected]...
 
Top