Setting filter prop. of subreport in detail

  • Thread starter Thread starter Max Moor
  • Start date Start date
M

Max Moor

Hi All,
I have a report with a subreport in its detail section. I pass the
strings for the filter for the main report and the subreport to the Open
event code of the main report. I wanted to set the filter of the subreport
from there with code like:

Me!rsub_MyRsub.Report.Filter = varFilter

I get error 2455 (invalid reference. I assume this is because the
subreport isn't loaded yet when the main report's Open code runs? True?

So, how can I set this from the main form? I hate to put it in the
detail format event, because it would run for every record, and only needs
to one time.

Thoughts?

- Max
 
Hi Max. This is one is a bit of a chestnut.

Some ideas:

#1. Subreport reads filter values from a form.
The simplest solution is to use a form where the user enters the filter
values to be applied to both the main report and the subreport. The query
that feeds the subreport can then refer to the controls on the form in its
Criteria, e.g.:
[Forms].[Form1].[Text1]
As long as the form remains open behind the report, the subreport can read
these values as often as it needs them.

#2. Subreport filtered by additional LinkMasterFields.
If the subreport should filter on a single value (not a range of dates for
example), another approach is to use the Open event of the main report to
set the LinkMasterFields and LinkChildFields of the subreport control. This
assumes you have a text box on the main report that contains the value to
filter.

#3. Subreport's query modified before opening the report.
If neither of these approaches work, another trick is to alter the SQL
property of the query for the subreport before the report is opened:
strSQL = "SELECT ...
dbEngine(0)(0).QueryDefs("Query2").SQL = strSQL
DoCmd.OpenReport "Report1", acViewPreview
Because you can write whatever you want into the SQL string, it's a very
efficient way to filter it.

#4. Temp table.
That covers nearly every case. If you have anything worse than that, you
probably have to write a temp table of the primary key values of the records
to use in the subreport.
 
Hi Max. This is one is a bit of a chestnut.

Some ideas:

#1. Subreport reads filter values from a form.
The simplest solution is to use a form where the user enters the
filter values to be applied to both the main report and the subreport.
The query that feeds the subreport can then refer to the controls on
the form in its Criteria, e.g.:
[Forms].[Form1].[Text1]
As long as the form remains open behind the report, the subreport can
read these values as often as it needs them.
Click to expand...


Hi Allen,
I think this is the one. I do some of this already for the main
form, so I've already gone through the exercises to make sure it's open.

Let me ask you, should I do the call in the subreport's Open code? I
want to avoid having the code run on every formatting of the main report's
detail section (if there's a way around it). I don't quite get when Open
is run for a subreport.

Thanks for the help... Max
 
I went with #3 after all, and redefined the subreport's query, after all.
For some reason, I kept getting an error about the value I was setting the
subreports Filter to being inappropriate for the property. It was a valid
filter (WHERE) string. In fact, identical to the one in the main report.
There's obviously something I don't know about subreports.

Anyway, redefining the subreport's query works, so there you go.

Thanks for the help,

Max
 
No. You can't use the Open event of the subreport for this kind of thing.

#1 is the way to go where possible. If the code was complaining about the
filter, you could try deleting the module of the report if you no longer
need it, i.e. set the report's HasModule property to No. Then clear the
filter property (in design view).

#3 is useful for awkward spots. Constantly changing this property of the
querydef may have a slight bloating effect on the front end database.

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

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

Max Moor said:
Hi Max. This is one is a bit of a chestnut.

Some ideas:

#1. Subreport reads filter values from a form.
The simplest solution is to use a form where the user enters the
filter values to be applied to both the main report and the subreport.
The query that feeds the subreport can then refer to the controls on
the form in its Criteria, e.g.:
[Forms].[Form1].[Text1]
As long as the form remains open behind the report, the subreport can
read these values as often as it needs them.
Click to expand...


Hi Allen,
I think this is the one. I do some of this already for the main
form, so I've already gone through the exercises to make sure it's open.

Let me ask you, should I do the call in the subreport's Open code? I
want to avoid having the code run on every formatting of the main report's
detail section (if there's a way around it). I don't quite get when Open
is run for a subreport.

Thanks for the help... Max
Click to expand...
 
Back
Top