Opening subreports using VBA

[J. Toews]

Hi Everyone..

We have recently switched from Access 97 to 2002. I have a report that
contains multiple sub-reports. Before the user opens the report they select
what reporting options they want to see on it. The user's selection provides
what record source(s) are used in the main and sub-reports and what
sub-reports property will be set to visible or not visible on the main
report. This is all done through VBA.

In Access 97 what I would do in code was open the main report in design
mode, set whatever sub-reports visible property accordingly, open and change
the appropriate sub-report's record source, save and close the sub-report(s)
and then save and close the main report.

My code I used in 97 doesn't work in 2002 because of the new "FEATURE" where
once you have the main report open in design mode you have to open any
sub-reports that are attached to it in a seperate window.

Below is a little snip of my code. Note: this code is part of a properly
compliled sub-procedure that worked perfectly in Access 97, please don't
respond back that I am missing a End Sub or something silly like that, just
assume it worked. Also the InitializeSubReport routine I call opens, in
design mode, the passed sub-report name, update its record source and then
saves/closes it.

------------------------------------------------------------------
DoCmd.OpenReport "Revenue 4a", acViewDesign
Reports("Revenue 4a").RecordSource = sFinalSql

'change report RecordSources according to passed variable
Select Case iRevenueReportType
Case 1:
Call InitializeSubReport("Revenue 4b", lsBeginSql)
Call InitializeSubReport("Revenue 4c", lsBeginSql)
Call InitializeSubReport("Revenue 4d", lsBeginSql)

Reports![Revenue 4a].child4.Visible = True
Call InitializeSubReport("Revenue 4e", lsReconcileSql)

'subreports 4f and 4g not needed
Reports![Revenue 4a].Child5.Visible = False
Reports![Revenue 4a].child6.Visible = False

Case 2:
Call InitializeSubReport("Revenue 4b", lsBeginSql)
Call InitializeSubReport("Revenue 4c", lsBeginSql)
Call InitializeSubReport("Revenue 4d", lsBeginSql)

Reports![Revenue 4a].Child5.Visible = True
Call InitializeSubReport("Revenue 4f", lsReconcileSql)

'subreports 4e and 4g not needed
Reports![Revenue 4a].child4.Visible = False
Reports![Revenue 4a].child6.Visible = False
End Select
DoCmd.Save acReport, "Revenue 4a"

------------------------------------------------------------------

So the question is: do I have to rewrite this code to open up the subreports
first, make the necessary changes, save/close them and then open up the main
report and make the necessay changes and then save/close it? Or is there a
way to open up sub-reports through VBA in Access 2002 and change the record
sources while the main report is still open. The above code gives me the,
"You have to open up the sub-report in a new window" error.

The bottom line here is I would prefer not to have to re-write the code if I
don't have to because this above code is repeated multiple time for umpteen
different reports. And that's a hell of a re-write!!

Any help would be greatly appreciated,

Thanking you in advance..

JT.

 

[Rick Brandt]

Hi Everyone..

We have recently switched from Access 97 to 2002. I have a report that
contains multiple sub-reports. Before the user opens the report they select
what reporting options they want to see on it. The user's selection provides
what record source(s) are used in the main and sub-reports and what
sub-reports property will be set to visible or not visible on the main
report. This is all done through VBA.

In Access 97 what I would do in code was open the main report in design
mode, set whatever sub-reports visible property accordingly, open and change
the appropriate sub-report's record source, save and close the sub-report(s)
and then save and close the main report.

AACK!

It is only necessary to open a report in design view to make such changes
if you want them to be permanent. For on-the-fly changes just put the code
in the Open event of the appropriate reports and it will work without
needing to go to design view.

 

[J. Toews]

Well, either way it looks like I have some work ahead of me, which I was
hoping to avoid.. Oh well, such is life..

Thanks for the reply Rick!!..