Referencing Properties of Nested Subreports

[Guest]

I need to dynamically control whether or not various subreports are visible
and what their SourceObject is set to. The report I'm working on has four
different levels. The subreports that I want to control are at the lowest
(4th) level. The code I'm using is:

Dim dbs As Database
Dim rst As Recordset, rst2 As Recordset
Dim strSQL As String, strSQL2 As String
Dim Counter As Byte

Set dbs = CurrentDb

'Open a recordset that contains the subreports to turn on. Loop through the
recordset to identify each subreport that
'has been selected and use the rptName as the SourceObject. Use another
recordset that contains the main report's
'subreport structure to identify which Subreport gets which SourceObject.

strSQL = "SELECT CustRptSelectSubs.* " _
& "FROM CustRptSelectSubs " _
& "WHERE Survey_Type = 'Laundry' " _
& "AND CustRptSelectSubs.[Select] = Yes " _
& "ORDER BY SortOrder;"
Set rst = dbs.OpenRecordset(strSQL)
rst.MoveFirst
Counter = 1
Do Until rst.EOF
strSQL2 = "SELECT * FROM CustRptSubReportSetup " _
& "WHERE Survey_Type = 'Laundry' " _
& "AND SectionOrder = " & Counter & ";"
Set rst2 = dbs.OpenRecordset(strSQL2)

Reports("rptCustomerMain")("LaundrySub")
("rptCustomerLaundryMachineInfo")(rst2!SubRptName)
.Visible = True

Reports("rptCustomerMain")("LaundrySub")
("rptCustomerLaundryMachineInfo")(rst2!SubRptName)
.SourceObject = "Report." & rst![RptName]

rst2.Close
Counter = Counter + 1
rst.MoveNext
Loop
rst.Close

If I run the code at the 3rd level (OnOpen event) I get an error that says
"the SourceObject cannot be set once printing has started". If I run the
code at the 1st level (OnOpen event)I get an error that says "you entered an
expression that has an invalid reference to the property form/report".
Comments/suggestions? Thanks.

 

[Marshall Barton]

I need to dynamically control whether or not various subreports are visible
and what their SourceObject is set to. The report I'm working on has four
different levels. The subreports that I want to control are at the lowest
(4th) level. The code I'm using is:

Dim dbs As Database
Dim rst As Recordset, rst2 As Recordset
Dim strSQL As String, strSQL2 As String
Dim Counter As Byte

Set dbs = CurrentDb

'Open a recordset that contains the subreports to turn on. Loop through the
recordset to identify each subreport that
'has been selected and use the rptName as the SourceObject. Use another
recordset that contains the main report's
'subreport structure to identify which Subreport gets which SourceObject.

strSQL = "SELECT CustRptSelectSubs.* " _
& "FROM CustRptSelectSubs " _
& "WHERE Survey_Type = 'Laundry' " _
& "AND CustRptSelectSubs.[Select] = Yes " _
& "ORDER BY SortOrder;"
Set rst = dbs.OpenRecordset(strSQL)
rst.MoveFirst
Counter = 1
Do Until rst.EOF
strSQL2 = "SELECT * FROM CustRptSubReportSetup " _
& "WHERE Survey_Type = 'Laundry' " _
& "AND SectionOrder = " & Counter & ";"
Set rst2 = dbs.OpenRecordset(strSQL2)

Reports("rptCustomerMain")("LaundrySub")
("rptCustomerLaundryMachineInfo")(rst2!SubRptName)
.Visible = True

Reports("rptCustomerMain")("LaundrySub")
("rptCustomerLaundryMachineInfo")(rst2!SubRptName)
.SourceObject = "Report." & rst![RptName]

rst2.Close
Counter = Counter + 1
rst.MoveNext
Loop
rst.Close

If I run the code at the 3rd level (OnOpen event) I get an error that says
"the SourceObject cannot be set once printing has started". If I run the
code at the 1st level (OnOpen event)I get an error that says "you entered an
expression that has an invalid reference to the property form/report".
Comments/suggestions? Thanks.

If there's any hope for this arrangement, the code to set
the SourceObject must be in the Open event of the report
that contains the subreport control. The code that changes
a report's RecordSource must be in that same report's Open
event. The key here is that each level is opened before any
of it's subreports and once opened the first time, can't be
changed. (Note that this does not apply to many control
properties such as position, font, etc.)

Tthe error message about not changing the SourceObject and
RecordSource properties once printing starts means what it
says. The Open event of a subreport (with Link Master/Child
set) fires for each instance of a subreport in the container
report, BUT these properties can only be set in the first
instance. This means that if you're trying to use a
different subreport in each detail, you can not use this
approach. Instead, place all the subreports in the detail
and make them visible or not as needed. Might be
unacceptably slow, but I don't know of any other way.

If you only want to select the same subreport for the entire
main report, then you can use code to make sure that the
open event only tries to set these properties once:

Static Initialized As Boolean
If Not Initialized Then
Me.RecordSource = . . .
Initialized = True
End If