How do I apply a filter to sub reports of a report i am opening?

[Guest]

I have a access report which has 2 sub reports. I want to apply a different
filter to each of the sub reports and the main report. I prefer to do this in
VBA but I am open to other suggestions. The only way I can figure out is by
using a macro or VBA to open each report in design mode first and then set
the filter. Seems a long way around it. Is this the only way?

 

[Allen Browne]

If you cannot work through the LinkMasterFields/LinkChildFields properties
of the subreports, you can write the SQL property of the queries they are
based on, e.g.:
dbEngine(0)(0).QueryDefs("Query1").SQL = "SELECT ...

That has the advantage that it works in an MDE, whereas opening the report
in design view does not.

 

[Guest]

Thanks for your reply Allen. Unfortunately I have had no computer training. I
am competent in access and have only worked out the basics in VBA so your
example doesn't mean much to me. LinkMasterFields/LinkChildFields properties
of the subreports will not do what I want. Maybe if I told you a few more
details you might be able to give me a example that I could understand? My
database is called "Contracts DB" the main report is called "Contract" and
the two sub reports are "Contract Sub" and "Contract QA Sub". Maybe I’m just
in over my head on this one?

 

[Allen Browne]

We don't know what is the difference between Contract Sub and Contract QA
Sub.

In order of preference, the choices would be:
1. Use the LinkMasterFields/LinkChildFields if possible.
It's not always possble, but covers about 90% of cases.

2. Use parameters in the subreport's queries.
These parameters can pick up values from a form where the user can enter the
limiting values.

3. Re-write the SQL statement.
To do this, you can mock up a query that gives an example of what you want.
Switch it to SQL view to see the example, and create a SQL statement as a
string in your VBA code (typically in the Click event of the button that
will open the report.) You can then assign this to the SQL property of the
query that the subreports are based on. If you do this before you
OpenReport, it effectively filters the subreports for the specific case you
are about to open.

 

[Guest]

I'll give it a go. Thanks.

We don't know what is the difference between Contract Sub and Contract QA
Sub.

In order of preference, the choices would be:
1. Use the LinkMasterFields/LinkChildFields if possible.
It's not always possble, but covers about 90% of cases.

2. Use parameters in the subreport's queries.
These parameters can pick up values from a form where the user can enter the
limiting values.

3. Re-write the SQL statement.
To do this, you can mock up a query that gives an example of what you want.
Switch it to SQL view to see the example, and create a SQL statement as a
string in your VBA code (typically in the Click event of the button that
will open the report.) You can then assign this to the SQL property of the
query that the subreports are based on. If you do this before you
OpenReport, it effectively filters the subreports for the specific case you
are about to open.