Referencing a subreport on Open of Main form

[Guest]

I am trying to change the recordsource of a subreport when the main report
opens. I have entered:
strSQL = "SELECT * FROM Jail_Record_Offense WHERE Release_Date is null"
Me.rsubOffenses.Report.RecordSource = strSQL

'rsubOffenses' is the name of the sub Report and it's underlying report name.

I get the error:
Run-time error '2455'
You entered an expression that has an invalid reference to the property
Form/Report.

What is wrong with my reference?

 

[Allen Browne]

There is a timing problem here.

You can set the RecordSource of a report only in its Open event. After that
is too late. The subreport's Open event fires before the main report's so
the main report's Open event is too late to set the source for the sub.

A workaround is to write the SQL property of the query the subreport is
based on before opening the report. For example, if the subreport's
RecordSource is Query1, and you open it by clicking a command button on a
form:

strSQL = "SELECT * FROM Jail_Record_Offense WHERE Release_Date is null"
dbEngine(0)(0).QueryDefs("Query1").SQL = strSql
DoCmd.OpenReport "Report1", acViewPreview