Sub-reports (1 Viewer)

[Hunter]

I have multiple sub-reports contained on a single parent report. What is the best way to open the main report and all subreports so that they reflect certain criteria set on a form from which they are opened. I am having trouble trying to use the "FilterOn" and "Recordsource" properties. I can get it working easily with a single report but the subreports are proving tricky. Any ideas...?

 

[Fornatian]

if you are trying to reflect certain criteria I would consider having your subreports based on queries that reference the forms fields for criteria.

does that help at all?

Ian

 

[Hunter]

Yes that would work, but what I am trying to get away from is creating a large number of reports or queries. Ideally, I would like to alter the "Recordsource" property based on the criteria selected on the form. I am having trouble getting this to work by embedding SQL statements in VBA code.

 

[Fornatian]

Whether it is a stored query or built in VBA can make a difference in performance.
Stored queries are what is called pre-optimised(Access knows the quickest execution) whereas SQL strings are executed 'as is' with no thought for how best to run them. This may or may not be a factor worth considering.

How many fields are you proposing to filter by? Do they always have values? Give us some further info(and maybe an example) and I'm sure we can help you.

 

[Hunter]

There are only two criteria that change. One has 3 possible values and the other has 2. This makes 6 possible variations when combining the two. There are 5 sub-reports on the main report. The main report acts only as a container and has no links to the sub-reports. I am trying not to do the simplest thing, which is to make 6X5=30 reports! What I envisioned doing was setting the criteria on a form, and then opening the report by passing these criteria to the main report. I thought I could alter the recordsource for each sub-report based on the chosen criteria on the form, but I haven’t been able to get it to work. The criteria for each sub-report are identical: Project Type (BT Projects, Non-BT, All Projects); and Include on Summary Report (Yes or No). Each of the 5 sub-reports is based on separate tables but the field names are identical in each table. Thanks for your help!!

 

[Fornatian]

Personally, the way I would do it is to build up the criteria into a filter string.
I've seen your answer to another question so you shouldn't have too much trouble doing that, something like:

In a public module set these variable
Public Dim strCrit1 as string
Public Dim strCrit2 as string
Pulbic Dim strCrit as string

strCrit1 = "[Project]= '"& Forms!unboundform!txtProject & "' AND "
'where Forms!unboundform!txtProject = your form criteria field

strCrit2 = "[IncSumm]]= "& Forms!unboundform!cbxIncSum
'where Forms!unboundform!txtProject = your form criteria check box.

strCrit = strCrit1 & strCrit2

In the OnOpen event of each of the five reports do this.

Me.Filter = strCrit

I think you may need to set the filter to On for all 5 reports by default or alternative set Me.FilterOn =True then refresh the report.

Also you'll need cleanup code on the close of the report to set the global variables to "".

You may not even need to do this if your code and criteria are static - you may be able to directly reference the form control and set the filter as:

"[Project]= '"& Forms!unboundform!txtProject & "' AND [IncSumm]]= "& Forms!unboundform!cbxIncSum

Keep me informed on your progress.

Ian