By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,354 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

How to change the RecordSource in a SubReport

P: n/a
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

Jun 27 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Jun 27 '08 #2

P: n/a
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




Jun 27 '08 #3

P: n/a
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

>
>



Jun 27 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.