set recordsource of subreport using VBA

[Chuck]

Hi,

I'm trying to set a subreport's RecordSource property from the parent report
but am getting this error:

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

The code is entered in the parent form's OnOpen event and uses this syntax:

Reports![report_name]![subreport_name].Report.ControlSource

The reference appears correct and if I use a dot instead of a bang when
typing in the code, Access auto-completes correctly (at least it uses the
same refenreces I used manually).

I've been reading up on this and apparently reports and subreports will give
inconsistent results (as opposed to setting a sub-form's properties from the
parent form - which I do regularly without any problems).

So now to my question.

I read an article that suggests using a class module to set the subreport's
ControlSource property from the parent report. I have no idea how to do
this so any pointers or example Db would be appreciated.

Thanks

Chuck

 

[Marshall Barton]

I'm trying to set a subreport's RecordSource property from the parent report
but am getting this error:

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

The code is entered in the parent form's OnOpen event and uses this syntax:

Reports![report_name]![subreport_name].Report.ControlSource

The reference appears correct and if I use a dot instead of a bang when
typing in the code, Access auto-completes correctly (at least it uses the
same refenreces I used manually).

I've been reading up on this and apparently reports and subreports will give
inconsistent results (as opposed to setting a sub-form's properties from the
parent form - which I do regularly without any problems).

So now to my question.

I read an article that suggests using a class module to set the subreport's
ControlSource property from the parent report. I have no idea how to do
this so any pointers or example Db would be appreciated.

You said RecordSource, but then use ControlSource everywhere
else??

Assuming you really mean RecordSource, then there are a
several facts you need to be aware of. First, unlike forms,
subreports are opened after the main report so they do not
yet exist at the time of the main report's Open event. This
means the subreport's RecordSource property can only be set
in the **subreport's** Open event.

Second, the RecordSource, and several other, properties can
not be set once the report starts printing (i.e. immediately
after the main report and subreport Open events are
completed).

Third, a subreport's Open event fires for each appearance in
the final report. This means that you need to use code in
the subreport's Open event so that the RecordSource is only
set the first time the event fires:

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

Most of the time all this fooling around is totally
unecessary beacuse the Link Master/Child Fields properties
take care of 99.9% of the situations.