Changing a subreport's RecordSource in code

  • Thread starter Thread starter Rob Rutherford
  • Start date Start date
R

Rob Rutherford

Access 2002. Can anyone tell me how to change a subreport's
RecordSource property in VBA code?

Both of the following in the Report_Open event give me an error.

Main report: Me.rsub.Report.RecordSource = "Table1"
Subreport: Me.RecordSource = "Table1"

Any advice would be appreciated.
 
Rob said:
Access 2002. Can anyone tell me how to change a subreport's
RecordSource property in VBA code?

Both of the following in the Report_Open event give me an error.

Main report: Me.rsub.Report.RecordSource = "Table1"
Subreport: Me.RecordSource = "Table1"
Click to expand...


You should tell us what error it gives you, but I'll guess
it's that you can't set it once prointing has started.

In that case, the message is correct and it means what it
says. The situation is a bit tricky in that you can set the
record surce in the subreport's Open event, BUT it can only
be done the first time the subreport is opened (the open
event fires for each instance of the subreport).

As long as you only want one record source for all instances
of the subreport, you can avoid the error by using code
like:

Sub Report_Open(
Static Initialized As Boolean
If Not Initialized Then
Me.RecordSource = "Table1"
Initialized = True
End If
End Sub

Warning: Setting the record source to different tables
smells like an improperly normalized data structure.
 
Thank you, your suggestion works fine.

For the record the errors were:

1) With code in main report
'You entered an expression that has an invalid reference to the property Form/Report.'

2) With code in subreport
'You can't set the RecordSource property in print preview or after printing has started.'

Sorry, I was being lazy there.

The particular subreport contains diagrams and the user is given the option of printing
the report with or without them. I've been using a query as the record source which is
customised dependent on the user's choice, but I was curious as to why I couldn't do
it using code in the RecordSource. I hadn't appreciated that the subreport's Open event
fires many times so thanks for clearing that up.

Regards,
Rob Rutherford
 
Back
Top