Changing a subreport recordsource

[NevilleT]

I have a report containing 13 subreports. Which subreports print is
determined by a form with 13 checkboxes. Tick the box, and the subreport's
visible property is set to true. The visible property is set by the reports
open event and works fine.

When I run the report, and try to print to Word I get an error message that
there are too many databases open. To overcome this I thought I would change
the recordset property of the subreports not required to blank so they would
not open a new query. If it is true the recordsource of the subreport is
changed to a query name.

If Forms![frmProjPlanRptSelector]![chkProjDetails] = True Then
Me!rptPPProjectSub.Report.RecordSource = "qryPPProjectSubreport"
Me![rptPPProjectSub].Visible = True
Else
Me![rptPPProjectSub].Report.RecordSource = ""
Me![rptPPProjectSub].Visible = False
End If

When I run it, the second line causes the following error. "Error 2455 You
entered an expression that has an invalid reference to the property
form/report"

What am I missing?

 

[Marshall Barton]

I have a report containing 13 subreports. Which subreports print is
determined by a form with 13 checkboxes. Tick the box, and the subreport's
visible property is set to true. The visible property is set by the reports
open event and works fine.

When I run the report, and try to print to Word I get an error message that
there are too many databases open. To overcome this I thought I would change
the recordset property of the subreports not required to blank so they would
not open a new query. If it is true the recordsource of the subreport is
changed to a query name.

If Forms![frmProjPlanRptSelector]![chkProjDetails] = True Then
Me!rptPPProjectSub.Report.RecordSource = "qryPPProjectSubreport"
Me![rptPPProjectSub].Visible = True
Else
Me![rptPPProjectSub].Report.RecordSource = ""
Me![rptPPProjectSub].Visible = False
End If

When I run it, the second line causes the following error. "Error 2455 You
entered an expression that has an invalid reference to the property
form/report"

The main report opens before the subreports, so it's too
soon to set a subreport's properties from the main report's
Open event (and any other main report event is too late).

Bottom line is that the only place you can do this is in
each subreport's Open event.

Unfortunately that's not the whole story. If there will be
more than one instance of the subreport (e.g. it's in the
detail section), then you have to add additional code to
make sure you only set the record source once. Fortunately,
that's pretty easy:

Static Initialized As Boolean
If Not Initialized Then
'do your thing here
Initialized = True
End If

One additional thought, it has been reported(?) that Access
might look at the record source before the open event. If
you see anything strange, try setting the subreport's
RecordSource to blank in design view so Access can't even
attempt to start processing it before you decide what to do
with it.