How to change the RecordSource in a SubReport

[Simon]

Dear reader,

The syntax for the VBA code to change the RecordSource of a Master Report
is:

Me.RecordSource = "TableOrQueryName"

But now the syntax to change the RecordSource of a SubReport:

I think it has to be something like this:

Reports.MasterReportName.SubReportName.RecordSourc e =
"TableOrQueryName"

But this is not working.

Is there a working syntax for this?

Tanks for any help.

Kind regards,

Simon

[Larry Linson]

You are aware, I presume, that there is no Access "Subreport" object. There
is a Subreport Control, into which either a Report or a Form can be
imbedded. Code in a module or event of the parent Reportwould address the
RecordSource of a Report or Form embedded into a Subreport Control is as
follows:

Reports!<yourreportname>!<subreportcontrolname>.Re port.RecordSource

Reports!<yourreportname>!<subreportcontrolname>.Fo rm.RecordSource

Me!<subreportcontrolname>.Report.RecordSource

OR

Me!<subreportcontrolname>.Form.RecordSource

But that would likely only add to your frustration, because at runtime you
can only change the RecordSource of a Report in that Report's own Open
event. Thus you'd need to put the code in the Open event of the Report/Form
embedded in the Subreport Control, as:

Me.RecordSource = whateveryouwanttosetitto

In a well-designed, well-implemented Access database it is so rare that
you'd need to change the RecordSource of a Report/Form embedded in a
Subreport Control, that I am obliged to ask you just what you are trying to
accomplish by doing so. It's entirely possible that someone can offer a
useful suggestion for accomplishing the same purpose in a different way.

Generally, Subform and Subreport controls are used for displaying
information related to the Information currently displayed in their parent
form or Report, and use the LinkMasterFields and LinkChildFields properties
of the Subform or Subreport Controls to synchronize them.

Note that I don't rule out that you have encoutered an exception, and really
do need to do what you are trying to do... but generally, in the past, when
this has been asked, what's needed is not to change the RecordSource, but to
change the layout/design of the data tables themselves.

Larry Linson
Microsoft Office Access MVP
"Simon" <Sv********@Versatel.nlwrote in message
news:48***********************@news.tele2.nl...

Dear reader,

The syntax for the VBA code to change the RecordSource of a Master Report
is:

Me.RecordSource = "TableOrQueryName"

But now the syntax to change the RecordSource of a SubReport:

I think it has to be something like this:

Reports.MasterReportName.SubReportName.RecordSourc e =
"TableOrQueryName"

But this is not working.

Is there a working syntax for this?

Tanks for any help.

Kind regards,

Simon

[Simon]

Lary,

My SubReport is synchronized with a LinkMasterField and LinkChildField.
If I put in the Open event of the SubReport

Me.RecordSource = "QueryName"

an error pups up. It seems to be the SubReport on open event will be
activated after each record in the MasterReport. So an error pups up with
the massage "You can't change the RecordSource after the report is already
open".

Do you have a sugestion how to manage that.
Thanks for any help.
Kind regards,
Simon.

"Larry Linson" <bo*****@localhost.notschreef in bericht
news:wOZ1k.11225$%Z1.813@trnddc05...

You are aware, I presume, that there is no Access "Subreport" object.

There

is a Subreport Control, into which either a Report or a Form can be
imbedded. Code in a module or event of the parent Reportwould address

the

RecordSource of a Report or Form embedded into a Subreport Control is as
follows:

Reports!<yourreportname>!<subreportcontrolname>.Re port.RecordSource

Reports!<yourreportname>!<subreportcontrolname>.Fo rm.RecordSource

Me!<subreportcontrolname>.Report.RecordSource

OR

Me!<subreportcontrolname>.Form.RecordSource

But that would likely only add to your frustration, because at runtime

you

can only change the RecordSource of a Report in that Report's own Open
event. Thus you'd need to put the code in the Open event of the

Report/Form

embedded in the Subreport Control, as:

Me.RecordSource = whateveryouwanttosetitto

In a well-designed, well-implemented Access database it is so rare that
you'd need to change the RecordSource of a Report/Form embedded in a
Subreport Control, that I am obliged to ask you just what you are trying

to

accomplish by doing so. It's entirely possible that someone can offer a
useful suggestion for accomplishing the same purpose in a different way.

Generally, Subform and Subreport controls are used for displaying
information related to the Information currently displayed in their

parent

form or Report, and use the LinkMasterFields and LinkChildFields

properties

of the Subform or Subreport Controls to synchronize them.

Note that I don't rule out that you have encoutered an exception, and

really

do need to do what you are trying to do... but generally, in the past,

when

this has been asked, what's needed is not to change the RecordSource,

but to

change the layout/design of the data tables themselves.

Larry Linson
Microsoft Office Access MVP
"Simon" <Sv********@Versatel.nlwrote in message
news:48***********************@news.tele2.nl...

Dear reader,

The syntax for the VBA code to change the RecordSource of a Master

Report

is:

Me.RecordSource = "TableOrQueryName"

But now the syntax to change the RecordSource of a SubReport:

I think it has to be something like this:

Reports.MasterReportName.SubReportName.RecordSourc e =
"TableOrQueryName"

But this is not working.

Is there a working syntax for this?

Tanks for any help.

Kind regards,

Simon

[Larry Linson]

No, I don't have a suggestion -- because it is "working as (I understand and
have understood since 1993 it to have been) designed."

The question I have is what in the world do you have in your data layout
that would cause you to _need_ to change the RecordSource in the related
child records joined to the key field in the master record you are
processing. Are the related records in the many side of the relationship in
different tables depending on the key value of the master table (thus
"hiding data in the object name")?

Larry Linson
Microsoft Office Access MVP
"Simon" <Sv********@Versatel.nlwrote in message
news:48***********************@news.tele2.nl...

Lary,

My SubReport is synchronized with a LinkMasterField and LinkChildField.
If I put in the Open event of the SubReport

Me.RecordSource = "QueryName"

an error pups up. It seems to be the SubReport on open event will be
activated after each record in the MasterReport. So an error pups up with
the massage "You can't change the RecordSource after the report is already
open".

Do you have a sugestion how to manage that.
Thanks for any help.
Kind regards,
Simon.

"Larry Linson" <bo*****@localhost.notschreef in bericht
news:wOZ1k.11225$%Z1.813@trnddc05...

You are aware, I presume, that there is no Access "Subreport" object.

There

is a Subreport Control, into which either a Report or a Form can be
imbedded. Code in a module or event of the parent Reportwould address

the

RecordSource of a Report or Form embedded into a Subreport Control is

as

follows:
>
Reports!<yourreportname>!<subreportcontrolname>.Re port.RecordSource
>
Reports!<yourreportname>!<subreportcontrolname>.Fo rm.RecordSource
>
Me!<subreportcontrolname>.Report.RecordSource
>
OR
>
Me!<subreportcontrolname>.Form.RecordSource
>
But that would likely only add to your frustration, because at runtime

you

can only change the RecordSource of a Report in that Report's own Open
event. Thus you'd need to put the code in the Open event of the

Report/Form

embedded in the Subreport Control, as:
>
Me.RecordSource = whateveryouwanttosetitto
>
In a well-designed, well-implemented Access database it is so rare that
you'd need to change the RecordSource of a Report/Form embedded in a
Subreport Control, that I am obliged to ask you just what you are

trying
to

accomplish by doing so. It's entirely possible that someone can offer

a

useful suggestion for accomplishing the same purpose in a different

way.

>
Generally, Subform and Subreport controls are used for displaying
information related to the Information currently displayed in their

parent

form or Report, and use the LinkMasterFields and LinkChildFields

properties

of the Subform or Subreport Controls to synchronize them.
>
Note that I don't rule out that you have encoutered an exception, and

really

do need to do what you are trying to do... but generally, in the past,

when

this has been asked, what's needed is not to change the RecordSource,

but to

change the layout/design of the data tables themselves.
>
Larry Linson
Microsoft Office Access MVP
>
>
"Simon" <Sv********@Versatel.nlwrote in message
news:48***********************@news.tele2.nl...

Dear reader,



The syntax for the VBA code to change the RecordSource of a Master

Report

is:



Me.RecordSource = "TableOrQueryName"



But now the syntax to change the RecordSource of a SubReport:

I think it has to be something like this:



Reports.MasterReportName.SubReportName.RecordSourc e =
"TableOrQueryName"



But this is not working.

Is there a working syntax for this?



Tanks for any help.

Kind regards,

Simon

>
>