Change RecordSource of Sub-Report

  • Thread starter Thread starter robc
  • Start date Start date
R

robc

I am trying to change the RecordSource of a Sub-Report at runtime...

In the On Open Report section I have coded...

Reports![MainReportName]![SubReportName].Report.RecordSource = "qrySource1"

The error message I am getting is

RunTime Error 2455
You entered an expression tha has an invalid reference to the property
Form/Report

Any Ideas !
 
The subreport opens before the main report, so this approach probably won't
work.

Would it be possible to write the SQL property of the query before opening
the report? If the user opens the report from a form:
dbEngine(0)(0).QueryDefs("qrySource1").SQL = "SELECT ...

Alternativley, could the query read the criteria directly from the form? For
example, you might enter this into the Criteria row of your form:
Between [Forms]![MyForm]![StartDate] And [Forms]![MyForm]![EndDate]
 
Thanks for responding...

After more research I created the following solution based on MS KB article
210285. It works, but I do not know why. Maybe you could shed a little
light.



In the OnOpen event of the SUB report....

Private Sub Report_Open(Cancel As Integer)

Static intCall As Long

If [Forms]![frmReports3]![optRep] = 1 Then
If intCall = 0 Then Me.RecordSource = "qryArTrnSub2"
intCall = intCall + 1
End If
If [Forms]![frmReports3]![optRep] = 2 Then
If intCall = 0 Then Me.RecordSource = "qryArTrnSub2"
intCall = intCall + 1
End If
If [Forms]![frmReports3]![optRep] = 3 Then
If intCall = 0 Then Me.RecordSource = "qryArTrnSub2Asel"
intCall = intCall + 1
End If

End Sub




Allen Browne said:
The subreport opens before the main report, so this approach probably won't
work.

Would it be possible to write the SQL property of the query before opening
the report? If the user opens the report from a form:
dbEngine(0)(0).QueryDefs("qrySource1").SQL = "SELECT ...

Alternativley, could the query read the criteria directly from the form? For
example, you might enter this into the Criteria row of your form:
Between [Forms]![MyForm]![StartDate] And [Forms]![MyForm]![EndDate]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

robc said:
I am trying to change the RecordSource of a Sub-Report at runtime...

In the On Open Report section I have coded...

Reports![MainReportName]![SubReportName].Report.RecordSource = "qrySource1"

The error message I am getting is

RunTime Error 2455
You entered an expression tha has an invalid reference to the property
Form/Report
Click to expand...
Click to expand...
 
The Open event of the subreport may be called multiple times.

The code uses a static variable so that it responds only the first time it
is called. At that time it is able to read the value of the option group and
assing the recordsource. The 2nd time it is called, the static variable is
already 1, so the code does not run, and the error does not occur (i.e. it
is too late to assign the RecordSource on the 2nd call.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

robc said:
Thanks for responding...

After more research I created the following solution based on MS KB article
210285. It works, but I do not know why. Maybe you could shed a little
light.



In the OnOpen event of the SUB report....

Private Sub Report_Open(Cancel As Integer)

Static intCall As Long

If [Forms]![frmReports3]![optRep] = 1 Then
If intCall = 0 Then Me.RecordSource = "qryArTrnSub2"
intCall = intCall + 1
End If
If [Forms]![frmReports3]![optRep] = 2 Then
If intCall = 0 Then Me.RecordSource = "qryArTrnSub2"
intCall = intCall + 1
End If
If [Forms]![frmReports3]![optRep] = 3 Then
If intCall = 0 Then Me.RecordSource = "qryArTrnSub2Asel"
intCall = intCall + 1
End If

End Sub




Allen Browne said:
The subreport opens before the main report, so this approach probably won't
work.

Would it be possible to write the SQL property of the query before opening
the report? If the user opens the report from a form:
dbEngine(0)(0).QueryDefs("qrySource1").SQL = "SELECT ...

Alternativley, could the query read the criteria directly from the form? For
example, you might enter this into the Criteria row of your form:
Between [Forms]![MyForm]![StartDate] And [Forms]![MyForm]![EndDate]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

robc said:
I am trying to change the RecordSource of a Sub-Report at runtime...

In the On Open Report section I have coded...

Reports![MainReportName]![SubReportName].Report.RecordSource = "qrySource1"

The error message I am getting is

RunTime Error 2455
You entered an expression tha has an invalid reference to the property
Form/Report
Click to expand...
Click to expand...
Click to expand...
 
Back
Top