Selective subreports

[rossco]

Not sure if that's the best subject heading but I am trying to automate the appearance of subreports based on a field value. For example, if a specific field on a subreport contains the numbers 1 to 5 then the relevant subreport needs to appear - if any other number, then it needs to be hidden. I've tried code on the master report and on each subreport but with no success.

Many thanks

[HansV]

A subreport cannot hide itself, so you'll have to do it from the main report. Assuming that the subreport is in the Detail section of the main report, you'll have to write code in the On Format event of the Detail section of the main report that determines whether the record source of the subreport contains one of the numbers 1 to 5 and sets the Visible property of the subreport control based on this.

If you want more specific help, I'll need to have more detailed information.

[rossco]

Thank you.

Within the On Format event of the main report I've tried:

Code: Select all

If .[subrpt2].IDfield = 0 Then
            .subrpt2.Visible = False
        Else
            .subrpt2.Visible = True
        End If

but I get the error "property or method not supported".

I then tried dlookup using the table recordset in the if statement but that returned an empty set.

[HansV]

You can't refer to a control in a subreport from the main report. A subreport can, in theory at least, display multiple records, so "the" value of a control in a subreport isn't defined anyway.

Can you try to explain clearly and in detail what exactly you are trying to accomplish?

[rossco]

My work still relates to the questions database. The data entry part is working fine, thank you again, Hans, but now I need to start on the reports and the first is a print out of the relevant questions. Each q&a session may have to 5 questions and so that people can carry a clipboard around, answer the questions on paper and complete the data entry later, the report needs to replicate the data entry screen and show only the questions pertinent to the current review.

[HansV]

Thanks, that makes it clear what you want to do.

You don't really need subreports for this. You can create a report based on a query that joins the main observations and subobservations tables; if there are no active subobservations, the main observation will automatically be suppressed.

See the report in the attached version. Note that no sort order has been specified in the query; the sort order is specified in the Sorting and Grouping window of the report.

Since you want to print the report and use the printout for paper data entry, I have used rectangles for the options, they are a bit larger than check boxes or radio buttons.

Observe_2010-07-07C.zip

[rossco]

Of course! I can see that I was making it too hard and that in this case at least, a simple, direct approach is best!

Many thanks yet again.