How to change the RecordSource in a SubReport

**Larry Linson** · Jun 27 '08, 07:26 PM

Re: How to change the RecordSource in a SubReport

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!<yourre portname>!<subr eportcontrolnam e>.Report.Recor dSource

Reports!<yourre portname>!<subr eportcontrolnam e>.Form.RecordS ource

Me!<subreportco ntrolname>.Repo rt.RecordSource

OR

Me!<subreportco ntrolname>.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 = whateveryouwant tosetitto

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 LinkMasterField s 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" <SvanBeekNL@Ver satel.nlwrote in message
news:48486294$0 $28257$bf4948fe @news.tele2.nl. ..

Dear reader,
>
>
>
The syntax for the VBA code to change the RecordSource of a Master Report
is:
>
>
>
Me.RecordSource = "TableOrQueryNa me"
>
>
>
But now the syntax to change the RecordSource of a SubReport:
>
I think it has to be something like this:
>
>
>
Reports.MasterR eportName.SubRe portName.Record Source =
"TableOrQueryNa me"
>
>
>
But this is not working.
>
Is there a working syntax for this?
>
>
>
Tanks for any help.
>
Kind regards,
>
Simon
>
>
>

**Simon** · Jun 27 '08, 07:26 PM

Re: How to change the RecordSource in a SubReport

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" <bouncer@localh ost.notschreef in bericht
news:wOZ1k.1122 5$%Z1.813@trndd c05...

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!<yourre portname>!<subr eportcontrolnam e>.Report.Recor dSource
>
Reports!<yourre portname>!<subr eportcontrolnam e>.Form.RecordS ource
>
Me!<subreportco ntrolname>.Repo rt.RecordSource
>
OR
>
Me!<subreportco ntrolname>.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 = whateveryouwant tosetitto
>
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 LinkMasterField s 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" <SvanBeekNL@Ver satel.nlwrote in message
news:48486294$0 $28257$bf4948fe @news.tele2.nl. ..

Dear reader,

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

Report

is:

Me.RecordSource = "TableOrQueryNa me"

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

I think it has to be something like this:

Reports.MasterR eportName.SubRe portName.Record Source =
"TableOrQueryNa me"

But this is not working.

Is there a working syntax for this?

Tanks for any help.

Kind regards,

Simon

>
>

**Larry Linson** · Jun 27 '08, 07:26 PM

Re: How to change the RecordSource in a SubReport

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" <SvanBeekNL@Ver satel.nlwrote in message
news:4848c221$0 $22564$bf4948fe @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" <bouncer@localh ost.notschreef in bericht
news:wOZ1k.1122 5$%Z1.813@trndd c05...

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!<yourre portname>!<subr eportcontrolnam e>.Report.Recor dSource
>
Reports!<yourre portname>!<subr eportcontrolnam e>.Form.RecordS ource
>
Me!<subreportco ntrolname>.Repo rt.RecordSource
>
OR
>
Me!<subreportco ntrolname>.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 = whateveryouwant tosetitto
>
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 LinkMasterField s 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" <SvanBeekNL@Ver satel.nlwrote in message
news:48486294$0 $28257$bf4948fe @news.tele2.nl. ..

Dear reader,

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

Report

is:

Me.RecordSource = "TableOrQueryNa me"

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

I think it has to be something like this:

Reports.MasterR eportName.SubRe portName.Record Source =
"TableOrQueryNa me"

But this is not working.

Is there a working syntax for this?

Tanks for any help.

Kind regards,

Simon

>
>

>
>
>

How to change the RecordSource in a SubReport

How to change the RecordSource in a SubReport

Comment

Comment

Comment