Change filter in OpenReport

  • Thread starter Thread starter Christopher Caswell
  • Start date Start date
C

Christopher Caswell

I have a sub report that I want to filter when it opens, based on the
OpenARges of the parent form. I get an error that "the setting isn't valid
for the property". What am I doing wrong? Is there a better approach?
I've achieved success by changing the record source but thought the filter
would be simpler/more straightforward.

Chris
 
Why not use the WhereCondition of the OpenReport action, rather than
OpenArgs (which is not even present with earlier versions of Access)?

The filter string passed in the WhereCondition argument works fine, except
that Access does not properly maintain the FilterOn property, which means
that you cannot tell programmatically if the filter is actually applied or
not.
 
IMHO filtering a subform is often best accomplished by using the
MasterLinkFields and ChildLinkFields of the subform control.
The problem becomes how to put your filter information into the main form in
a way that it can be seen by the subform. I have used a hidden control for
this.
I've had only limited success, however, in changing the value of controls in
the report's Open event. I've been able to set the caption of labels, but
not the value of a textbox, which I think is what's required here.

If this report is always being called from the same form, you could drop the
use of OpenArgs, and instead set a direct reference to a control on that
calling form. (i.e. set your hidden textbox's controlsource to
Forms!CallingForm!FilterValue)
Please post back if you need to call the report from a variety of forms. I
have some more (albeit untested) ideas.

HTH
- Turtle
 
Ah. You're trying to pass the OpenArgs from the main to the sub report.
There is a timing issue there, i.e. Access opens the subreport before the
main report.

Applying the main report's filter to the sub is a perennial problem. The
simplest workaround is usually to use a form where the user enters all the
applicable criteria in different controls, and then have the subreport's
source query read the values from the form. The query contains things like:
SELECT * FROM MySubTable WHERE SomeField = Forms!MyForm!SomeControl;

Although I have not tried using OpenArgs as you suggest, it would be
possible. If we are talking about a single value always applied to the same
field of the subreport, you could put a Text box on main report with
ControlSource of:
=[Report].[OpenArgs]
and name it in the LinkMasterFields property of the subreport, with the
sub's matching field in LinkChildFields.

If it is a complex argument, is should be possible to parse the values in
Report_Open, write them to a number of unbound text boxes, and assign
LinkMasterFields/LinkChildFields.

For really involved criteria where none of these approaches are suitable,
you do have another possibility. Assuming the user always opens the report
from a form, you can simply write the SQL property of the query the
subreport uses so that it returns only the desired records for this run.
 
Back
Top