Change Recordsource of Subreport

[Alan Z. Scharf]

Hi,

I'd like to change the recordsource of a subreport depending on the value of
a field in the main report.

However, the following line in the main form OnOpen event produces error
below.

Me!srptAnnualPerformanceCrosstab.Report.RecordSource = "SELECT * FROM
tblAnnualPerformanceCrosstab"

ERROR: 2455
You entered an invalid reference to the property form/report

This same syntax works in setting record source of a subForm, but it is not
working for my subReport here/


Any suggestions on how to change the subform's recordsource
programmatically?

Thanks.

Alan

 

[Marshall Barton]

I'd like to change the recordsource of a subreport depending on the value of
a field in the main report.

However, the following line in the main form OnOpen event produces error
below.

Me!srptAnnualPerformanceCrosstab.Report.RecordSource = "SELECT * FROM
tblAnnualPerformanceCrosstab"

ERROR: 2455
You entered an invalid reference to the property form/report

This same syntax works in setting record source of a subForm, but it is not
working for my subReport here/

Unlike forms, subreports are opened after the main report.
You hace to put the code to set the record source in the
subreport's Open event. But, it's not quite that easy. A
subreport's open event is triggered each time the subreport
is processed, usually in each detail and you can not change
the subreports critical properties (whatever they are?)
after the first time it is opened. You can use code like
this to deal with all this stuff:

Static Initialized As Boolean
Dim strSQL As String
If Not Initialized Then
strSQL = "SELECT * FROM tblAnnualPerformanceCrosstab"
Me.RecordSource = strSQL
Initialized = True
End If

Are you really sure you have to set the record source? The
Link Master/Child properties go a long way toward avoiding
this kind of situation.

 

[Alan Z. Scharf]

Marshall,

Thanks very much!

I will try your code today.


I'm trying to re-use a subreport, whose data source is identical for several
main forms which are using it, but needs a differeent record source string
for one particular main form using it.


Is there a way to trick the master/child properties into acting like a
recordsource?
Can those properties be set at run time as in forms?
Can more than one field be used in master/child properties, or would I have
to create compund column?


P.S. Since you are familiar with this area of linking Reports/Subreports,
would you be able to take a look at another posting I have in this group
that has been unanswered? Wrong MSGraph record occasionaly prints, even
though graph is linked to main form via Master/Child properties.

Subject: "Report Not Printing Proper Graph Record" - 06/18/2006

Thanks very much for your help.

Regards,

Alan

 

[Marshall Barton]

Comments inline below.
--
Marsh
MVP [MS Access]

I'm trying to re-use a subreport, whose data source is identical for several
main forms which are using it, but needs a differeent record source string
for one particular main form using it.


Is there a way to trick the master/child properties into acting like a
recordsource?

No, but they do act kind of like a condition in a Where
clause (technically an ON phrase in a equi-JOIN clause).
So, if your only reason for setting the record source is to
filter the data, then the Link Master/Child properties can
usually take care of it.

Can those properties be set at run time as in forms?

The Link Master/Child properties can be set in a form's open
event or, if done carefully, at other times, but, like the
record source, only once for reports. This is typically not
an issue because the Link Master property can refer to a
main form/report control that can be a calculated field or
you can set the text box's value as needed.

Can more than one field be used in master/child properties, or would I have
to create compund column?

You can specify multiple linking fields separated by a
semicolon. See Help on LinkMasterFields for details.

P.S. Since you are familiar with this area of linking Reports/Subreports,
would you be able to take a look at another posting I have in this group
that has been unanswered? Wrong MSGraph record occasionaly prints, even
though graph is linked to main form via Master/Child properties.

Subject: "Report Not Printing Proper Graph Record" - 06/18/2006

Sorry, but I do not have enough experience with graphs to
respond intelligently.

 

[Alan Z. Scharf]

Marshall,

Thanks once again for the additional info on master/child links.

Regards,

Alan

Comments inline below.
--
Marsh
MVP [MS Access]

I'm trying to re-use a subreport, whose data source is identical for several
main forms which are using it, but needs a differeent record source string
for one particular main form using it.


Is there a way to trick the master/child properties into acting like a
recordsource?

No, but they do act kind of like a condition in a Where
clause (technically an ON phrase in a equi-JOIN clause).
So, if your only reason for setting the record source is to
filter the data, then the Link Master/Child properties can
usually take care of it.

Can those properties be set at run time as in forms?

The Link Master/Child properties can be set in a form's open
event or, if done carefully, at other times, but, like the
record source, only once for reports. This is typically not
an issue because the Link Master property can refer to a
main form/report control that can be a calculated field or
you can set the text box's value as needed.

Can more than one field be used in master/child properties, or would I have
to create compund column?

You can specify multiple linking fields separated by a
semicolon. See Help on LinkMasterFields for details.

P.S. Since you are familiar with this area of linking Reports/Subreports,
would you be able to take a look at another posting I have in this group
that has been unanswered? Wrong MSGraph record occasionaly prints, even
though graph is linked to main form via Master/Child properties.

Subject: "Report Not Printing Proper Graph Record" - 06/18/2006

Sorry, but I do not have enough experience with graphs to
respond intelligently.

value
of is
not