hide subreport section conditionally

[Guest]

I have an application in which a user chooses values from combo boxes in a
dialog form say Frm1. These values then open the appropriate report and pass
the criteria to the queries underlying these reports. I have used macros to
hide certain sections in a report say Rpt1 by attaching a macro to the 'On
Open' property of Rpt1. This macro looks like:
condition:[Forms]![Frm1]![cbo1] Is Null And [Forms]![Frm1]![cbo2] Is Not Null
action: setvalue, Item:[Reports]![Rpt1].[Section](6).[Visible], expression:No

Now in Rpt1, there are 2 sub reports, say subrpt1, subrpt2 .
I am now trying to hide certain sections of a subreport say the report
footer of subrpt1 based on similar condition as above. I created another
similar macro as above for the subreport and tried attaching it to the 'On
Open' property of subrpt1 but on running, it errors out saying 'the object
that you referred in VB procedure as an OLE object is not an OLE object'. I
then removed it from 'On Open' property and placed it on the 'On Activate'
property of subrpt1, this time it didn't error out but doesn't bring in the
desired result that is it does not make the report footer in subrpt1
invisible.
Please advise.

 

[Steve Schapel]

Neeraj,

I don't think I have ever tried anythink like this with macros, so I am
not certain. But I would try in either the Open event of the main
report, or the Format event of the section of the main report where the
subreport is located. The syntax of the Item argument of the SetValue
macro action would be something like this...
[Reports]![Rpt1]![Subrpt1].[Report].[Section](6).[Visible]

 

[Guest]

Putting it in the 'format' event of the section of the main report that the
subreport was in did the job! Also I observed that your syntax for the item
argument was a bit different from the one that I was using for my main report
controls: the subreport control had [Report] following the subreport name. Is
that required for subreport controls?

Neeraj,

I don't think I have ever tried anythink like this with macros, so I am
not certain. But I would try in either the Open event of the main
report, or the Format event of the section of the main report where the
subreport is located. The syntax of the Item argument of the SetValue
macro action would be something like this...
[Reports]![Rpt1]![Subrpt1].[Report].[Section](6).[Visible]

--
Steve Schapel, Microsoft Access MVP

I have an application in which a user chooses values from combo boxes in a
dialog form say Frm1. These values then open the appropriate report and pass
the criteria to the queries underlying these reports. I have used macros to
hide certain sections in a report say Rpt1 by attaching a macro to the 'On
Open' property of Rpt1. This macro looks like:
condition:[Forms]![Frm1]![cbo1] Is Null And [Forms]![Frm1]![cbo2] Is Not Null
action: setvalue, Item:[Reports]![Rpt1].[Section](6).[Visible], expression:No

Now in Rpt1, there are 2 sub reports, say subrpt1, subrpt2 .
I am now trying to hide certain sections of a subreport say the report
footer of subrpt1 based on similar condition as above. I created another
similar macro as above for the subreport and tried attaching it to the 'On
Open' property of subrpt1 but on running, it errors out saying 'the object
that you referred in VB procedure as an OLE object is not an OLE object'. I
then removed it from 'On Open' property and placed it on the 'On Activate'
property of subrpt1, this time it didn't error out but doesn't bring in the
desired result that is it does not make the report footer in subrpt1
invisible.
Please advise.

 

[Steve Schapel]

Neeraj,

... the subreport control had [Report] following the subreport name. Is
that required for subreport controls?

Strictly speaking, it is not "required", but to ensure no ambiguity this
is the recommended syntax for controls on a subform or subreport.