How to change the RowSource in a SubReport

[Simon]

Dear reader,

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

Me.RowSource = "TableOrQueryName"

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

I think it has to be something like this:

Reports.MasterReportName.SubReportName.RowSource =
"TableOrQueryName"

But this is not working.

Is there a working syntax for this?

Tanks for any help.

Kind regards,

Simon

[Neil]

I assume you mean RecordSource, not RowSource. The syntax would be:

Reports!MasterReportName!SubReportControlName.Repo rt.Recordsource =
"asdfadsf"

You're actually referring to the "Report" property of the subreport control,
and then the RecordSource property of that report.
"Simon" <Sv********@Versatel.nlwrote in message
news:47***********************@news.tele2.nl...

Dear reader,

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

Me.RowSource = "TableOrQueryName"

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

I think it has to be something like this:

Reports.MasterReportName.SubReportName.RowSource =
"TableOrQueryName"

But this is not working.

Is there a working syntax for this?

Tanks for any help.

Kind regards,

Simon

[Stuart McCall]

"Simon" <Sv********@Versatel.nlwrote in message
news:47***********************@news.tele2.nl...

Dear reader,

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

Me.RowSource = "TableOrQueryName"

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

I think it has to be something like this:

Reports.MasterReportName.SubReportName.RowSource =
"TableOrQueryName"

But this is not working.

Is there a working syntax for this?

Tanks for any help.

Kind regards,

Simon

Reports!MasterReportName.SubReportName.Report.RowS ource ="TableOrQueryName"

Notice the addition of the word Report. This instructs Access to refer to
the report itself, not the subreport control that 'hosts' it.

[Simon]

If I use the syntax:
Reports!MasterReportName!SubReportControlName.Repo rt.Recordsource =
"asdfadsf"

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

And I have checked the names of the master en sub for report several times
and they are correct.
What can by wrong?
"Neil" <no****@nospam.netschreef in bericht
news:QW*****************@newssvr19.news.prodigy.ne t...

I assume you mean RecordSource, not RowSource. The syntax would be:

Reports!MasterReportName!SubReportControlName.Repo rt.Recordsource =
"asdfadsf"

You're actually referring to the "Report" property of the subreport

control,

and then the RecordSource property of that report.
"Simon" <Sv********@Versatel.nlwrote in message
news:47***********************@news.tele2.nl...

Dear reader,

The syntax for the VBA code to change the RowSource of a Master Report

is:

Me.RowSource = "TableOrQueryName"

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

I think it has to be something like this:

Reports.MasterReportName.SubReportName.RowSource =
"TableOrQueryName"

But this is not working.

Is there a working syntax for this?

Tanks for any help.

Kind regards,

Simon

[Neil]

The syntax is correct, but Access won't let you change the subreport's
recordsource after it's opened in the main report (was thinking of changing
other properties of a subreport when I wrote that).

So what you need to do is change the subreport's recordsource property from
within its own OnOpen event, rather than the parent report's OnOpen event.
You should be able to just go:

Me.Recordsource = "adfasdf"

in the subreport's OnOpen event.
"Simon" <Sv********@Versatel.nlwrote in message
news:47***********************@news.tele2.nl...

If I use the syntax:
Reports!MasterReportName!SubReportControlName.Repo rt.Recordsource =
"asdfadsf"

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

And I have checked the names of the master en sub for report several times
and they are correct.
What can by wrong?
"Neil" <no****@nospam.netschreef in bericht
news:QW*****************@newssvr19.news.prodigy.ne t...

>I assume you mean RecordSource, not RowSource. The syntax would be:

Reports!MasterReportName!SubReportControlName.Rep ort.Recordsource =
"asdfadsf"

You're actually referring to the "Report" property of the subreport

control,

>and then the RecordSource property of that report.
"Simon" <Sv********@Versatel.nlwrote in message
news:47***********************@news.tele2.nl...

Dear reader,

The syntax for the VBA code to change the RowSource of a Master Report

is:

>
Me.RowSource = "TableOrQueryName"

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

I think it has to be something like this:

Reports.MasterReportName.SubReportName.RowSource =
"TableOrQueryName"

But this is not working.

Is there a working syntax for this?

Tanks for any help.

Kind regards,

Simon

[Simon]

If I do so, change the subreport's recordsource in his own subreport's
recordsource in his own OnOpen event the next message pop's up:
"You can't set the record source property in print preview or after printing
has started."

And in case I change the query name to a not existing one the following
message pop's up:
"The record source "NotExistingQuery" specified on this form or report does
not exist."
So that means that the sytax is running.

But still a can't change the recordsource of the subreport.

"Neil" <no****@nospam.netschreef in bericht
news:fC*****************@newssvr19.news.prodigy.ne t...

The syntax is correct, but Access won't let you change the subreport's
recordsource after it's opened in the main report (was thinking of

changing

other properties of a subreport when I wrote that).

So what you need to do is change the subreport's recordsource property

from

within its own OnOpen event, rather than the parent report's OnOpen event.
You should be able to just go:

Me.Recordsource = "adfasdf"

in the subreport's OnOpen event.
"Simon" <Sv********@Versatel.nlwrote in message
news:47***********************@news.tele2.nl...

If I use the syntax:
Reports!MasterReportName!SubReportControlName.Repo rt.Recordsource =
"asdfadsf"

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

And I have checked the names of the master en sub for report several

times

and they are correct.
What can by wrong?
"Neil" <no****@nospam.netschreef in bericht
news:QW*****************@newssvr19.news.prodigy.ne t...

I assume you mean RecordSource, not RowSource. The syntax would be:

Reports!MasterReportName!SubReportControlName.Repo rt.Recordsource =
"asdfadsf"

You're actually referring to the "Report" property of the subreport

control,

and then the RecordSource property of that report.
"Simon" <Sv********@Versatel.nlwrote in message
news:47***********************@news.tele2.nl...
Dear reader,

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

Report

is:

Me.RowSource = "TableOrQueryName"

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

I think it has to be something like this:

Reports.MasterReportName.SubReportName.RowSource =
"TableOrQueryName"

But this is not working.

Is there a working syntax for this?

Tanks for any help.

Kind regards,

Simon

[lyle]

On Oct 8, 1:00 am, "Simon" <[e-mail]:

If I do so, change the subreport's recordsource in his own subreport's
recordsource in his own OnOpen event the next message pop's up:
"You can't set the record source property in print preview or after printing
has started."

You might try setting the sub-report's RecordSource in its module to a
Public Function as:
*****************
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = ShippersRecordSource()
End Sub
*****************

To open specify the Sub_Reports RecordSource while opening the Parent
Report you could use code in a Standard Module such as:
*****************
Dim mShippersRecordSource$

Public Sub OpenShippersParentReport(ByVal vRecordSource$)
mShippersRecordSource = vRecordSource
DoCmd.OpenReport "ShippersParentReport", acViewPreview
End Sub

Public Function ShippersRecordSource$()
ShippersRecordSource = mShippersRecordSource
End Function
*****************

You could then call the code with
*****************
Public Sub TestOpenShippersParentReport()
OpenShippersParentReport "SELECT * FROM Shippers WHERE CompanyName
LIKE 'S*'"
End Sub
*****************

[Neil]

I don't experience that. I'm using Access 2000, and, changing the
recordsource in the subreport's OnOpen event with the syntax:

Me.Recordsource = "TableOrQueryName"

changes it to that table or query and doesn't produce an error.

What version of Access are you in?
"Simon" <Sv********@Versatel.nlwrote in message
news:47**********************@news.tele2.nl...

If I do so, change the subreport's recordsource in his own subreport's
recordsource in his own OnOpen event the next message pop's up:
"You can't set the record source property in print preview or after
printing
has started."

And in case I change the query name to a not existing one the following
message pop's up:
"The record source "NotExistingQuery" specified on this form or report
does
not exist."
So that means that the sytax is running.

But still a can't change the recordsource of the subreport.

"Neil" <no****@nospam.netschreef in bericht
news:fC*****************@newssvr19.news.prodigy.ne t...

>The syntax is correct, but Access won't let you change the subreport's
recordsource after it's opened in the main report (was thinking of

changing

>other properties of a subreport when I wrote that).

So what you need to do is change the subreport's recordsource property

from

>within its own OnOpen event, rather than the parent report's OnOpen
event.
You should be able to just go:

Me.Recordsource = "adfasdf"

in the subreport's OnOpen event.
"Simon" <Sv********@Versatel.nlwrote in message
news:47***********************@news.tele2.nl...

If I use the syntax:
Reports!MasterReportName!SubReportControlName.Repo rt.Recordsource =
"asdfadsf"

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

And I have checked the names of the master en sub for report several

times

and they are correct.
What can by wrong?
"Neil" <no****@nospam.netschreef in bericht
news:QW*****************@newssvr19.news.prodigy.ne t...
I assume you mean RecordSource, not RowSource. The syntax would be:

Reports!MasterReportName!SubReportControlName.Rep ort.Recordsource =
"asdfadsf"

You're actually referring to the "Report" property of the subreport
control,
and then the RecordSource property of that report.
"Simon" <Sv********@Versatel.nlwrote in message
news:47***********************@news.tele2.nl...
Dear reader,

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

Report

is:

Me.RowSource = "TableOrQueryName"

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

I think it has to be something like this:

Reports.MasterReportName.SubReportName.RowSource =
"TableOrQueryName"

But this is not working.

Is there a working syntax for this?

Tanks for any help.

Kind regards,

Simon

[Simon]

My version of Access is 2003 (11.6566.6568) SP2

"Neil" <no****@nospam.netschreef in bericht
news:o3******************@newssvr13.news.prodigy.n et...

I don't experience that. I'm using Access 2000, and, changing the
recordsource in the subreport's OnOpen event with the syntax:

Me.Recordsource = "TableOrQueryName"

changes it to that table or query and doesn't produce an error.

What version of Access are you in?
"Simon" <Sv********@Versatel.nlwrote in message
news:47**********************@news.tele2.nl...

If I do so, change the subreport's recordsource in his own subreport's
recordsource in his own OnOpen event the next message pop's up:
"You can't set the record source property in print preview or after
printing
has started."

And in case I change the query name to a not existing one the following
message pop's up:
"The record source "NotExistingQuery" specified on this form or report
does
not exist."
So that means that the sytax is running.

But still a can't change the recordsource of the subreport.

"Neil" <no****@nospam.netschreef in bericht
news:fC*****************@newssvr19.news.prodigy.ne t...

The syntax is correct, but Access won't let you change the subreport's
recordsource after it's opened in the main report (was thinking of

changing

other properties of a subreport when I wrote that).

So what you need to do is change the subreport's recordsource property

from

within its own OnOpen event, rather than the parent report's OnOpen
event.
You should be able to just go:

Me.Recordsource = "adfasdf"

in the subreport's OnOpen event.
"Simon" <Sv********@Versatel.nlwrote in message
news:47***********************@news.tele2.nl...
If I use the syntax:
Reports!MasterReportName!SubReportControlName.Repo rt.Recordsource =
"asdfadsf"

I receive the Run-time error '2455'
You entered an expression that has an invalid reference to the

property

Form/Report.

And I have checked the names of the master en sub for report several

times

and they are correct.
What can by wrong?
"Neil" <no****@nospam.netschreef in bericht
news:QW*****************@newssvr19.news.prodigy.ne t...
I assume you mean RecordSource, not RowSource. The syntax would be:

Reports!MasterReportName!SubReportControlName.Repo rt.Recordsource =
"asdfadsf"

You're actually referring to the "Report" property of the subreport
control,
and then the RecordSource property of that report.
"Simon" <Sv********@Versatel.nlwrote in message
news:47***********************@news.tele2.nl...
Dear reader,

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

Report

is:

Me.RowSource = "TableOrQueryName"

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

I think it has to be something like this:

Reports.MasterReportName.SubReportName.RowSource =
"TableOrQueryName"

But this is not working.

Is there a working syntax for this?

Tanks for any help.

Kind regards,

Simon

[Neil]

I really don't understand then. Thought perhaps you had an older version of
Access.

Do you have anything else going on in the Open events in the main report or
subreport? If so, then please post that code.

Other than that, I'm not sure why it would be doing that.
"Simon" <Sv********@Versatel.nlwrote in message
news:47***********************@news.tele2.nl...

My version of Access is 2003 (11.6566.6568) SP2

"Neil" <no****@nospam.netschreef in bericht
news:o3******************@newssvr13.news.prodigy.n et...

>I don't experience that. I'm using Access 2000, and, changing the
recordsource in the subreport's OnOpen event with the syntax:

Me.Recordsource = "TableOrQueryName"

changes it to that table or query and doesn't produce an error.

What version of Access are you in?
"Simon" <Sv********@Versatel.nlwrote in message
news:47**********************@news.tele2.nl...

If I do so, change the subreport's recordsource in his own subreport's
recordsource in his own OnOpen event the next message pop's up:
"You can't set the record source property in print preview or after
printing
has started."

And in case I change the query name to a not existing one the following
message pop's up:
"The record source "NotExistingQuery" specified on this form or report
does
not exist."
So that means that the sytax is running.

But still a can't change the recordsource of the subreport.

"Neil" <no****@nospam.netschreef in bericht
news:fC*****************@newssvr19.news.prodigy.ne t...
The syntax is correct, but Access won't let you change the subreport's
recordsource after it's opened in the main report (was thinking of
changing
other properties of a subreport when I wrote that).

So what you need to do is change the subreport's recordsource property
from
within its own OnOpen event, rather than the parent report's OnOpen
event.
You should be able to just go:

Me.Recordsource = "adfasdf"

in the subreport's OnOpen event.
"Simon" <Sv********@Versatel.nlwrote in message
news:47***********************@news.tele2.nl...
If I use the syntax:
Reports!MasterReportName!SubReportControlName.Repo rt.Recordsource =
"asdfadsf"

I receive the Run-time error '2455'
You entered an expression that has an invalid reference to the

property

Form/Report.

And I have checked the names of the master en sub for report several
times
and they are correct.
What can by wrong?
"Neil" <no****@nospam.netschreef in bericht
news:QW*****************@newssvr19.news.prodigy.ne t...
I assume you mean RecordSource, not RowSource. The syntax would be:

Reports!MasterReportName!SubReportControlName.Rep ort.Recordsource =
"asdfadsf"

You're actually referring to the "Report" property of the subreport
control,
and then the RecordSource property of that report.
"Simon" <Sv********@Versatel.nlwrote in message
news:47***********************@news.tele2.nl...
Dear reader,

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

Me.RowSource = "TableOrQueryName"

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

I think it has to be something like this:

Reports.MasterReportName.SubReportName.RowSource =
"TableOrQueryName"

But this is not working.

Is there a working syntax for this?

Tanks for any help.

Kind regards,

Simon

[Simon]

I had no other code in the main and sub report as the

Me.RecourdSource = "QueryX" in OnOpen event of the MainReport

And

Me.RecourdSource = "QueryY" in OnOpen event of the SubReport

My coal was to avoid the situation that I need for each filter (query with
selection criteria) a different report.

So I have to change the RecordSource of the main and SubReport by checking
which query has to be filled out as RecordSource. But before I write this
code in the OnOpen of the MainReport I did a test of it works to change the
RecordSource in the MainReport and SubReport. And it came out that it works
in the Main Report and it dos not work in the SubReport. And because of this
I have now 6 the same reports with different selection queries as
RecordSource.

"Neil" <no****@nospam.netschreef in bericht
news:3w*******************@newssvr29.news.prodigy. net...

I really don't understand then. Thought perhaps you had an older version

of

Access.

Do you have anything else going on in the Open events in the main report

or

subreport? If so, then please post that code.

Other than that, I'm not sure why it would be doing that.
"Simon" <Sv********@Versatel.nlwrote in message
news:47***********************@news.tele2.nl...

My version of Access is 2003 (11.6566.6568) SP2

"Neil" <no****@nospam.netschreef in bericht
news:o3******************@newssvr13.news.prodigy.n et...

I don't experience that. I'm using Access 2000, and, changing the
recordsource in the subreport's OnOpen event with the syntax:

Me.Recordsource = "TableOrQueryName"

changes it to that table or query and doesn't produce an error.

What version of Access are you in?
"Simon" <Sv********@Versatel.nlwrote in message
news:47**********************@news.tele2.nl...
If I do so, change the subreport's recordsource in his own

subreport's

recordsource in his own OnOpen event the next message pop's up:
"You can't set the record source property in print preview or after
printing
has started."

And in case I change the query name to a not existing one the

following

message pop's up:
"The record source "NotExistingQuery" specified on this form or

report

does
not exist."
So that means that the sytax is running.

But still a can't change the recordsource of the subreport.

"Neil" <no****@nospam.netschreef in bericht
news:fC*****************@newssvr19.news.prodigy.ne t...
The syntax is correct, but Access won't let you change the

subreport's

recordsource after it's opened in the main report (was thinking of
changing
other properties of a subreport when I wrote that).

So what you need to do is change the subreport's recordsource

property

from
within its own OnOpen event, rather than the parent report's OnOpen
event.
You should be able to just go:

Me.Recordsource = "adfasdf"

in the subreport's OnOpen event.
"Simon" <Sv********@Versatel.nlwrote in message
news:47***********************@news.tele2.nl...
If I use the syntax:
Reports!MasterReportName!SubReportControlName.Repo rt.Recordsource

=

"asdfadsf"

I receive the Run-time error '2455'
You entered an expression that has an invalid reference to the

property

Form/Report.

And I have checked the names of the master en sub for report

several

times
and they are correct.
What can by wrong?
"Neil" <no****@nospam.netschreef in bericht
news:QW*****************@newssvr19.news.prodigy.ne t...
I assume you mean RecordSource, not RowSource. The syntax would

be:

>
Reports!MasterReportName!SubReportControlName.Repo rt.Recordsource

=

"asdfadsf"

You're actually referring to the "Report" property of the

subreport

control,
and then the RecordSource property of that report.
"Simon" <Sv********@Versatel.nlwrote in message
news:47***********************@news.tele2.nl...
Dear reader,

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

Me.RowSource = "TableOrQueryName"

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

I think it has to be something like this:

Reports.MasterReportName.SubReportName.RowSource =
"TableOrQueryName"

But this is not working.

Is there a working syntax for this?

Tanks for any help.

Kind regards,

Simon

[Neil]

OK, let's back up a second. You say that you're just trying to apply query
with selection criteria to your report? You should be able to do that
without changing the recordsource. Just place criteria in your query which
refers to the form that you are using for your selection criteria.

Post a description of the form you're using for selection criteria, along
with the form name and the names of the controls on the form, and I'll show
you how to set that up. You shouldn't have to change the recordsource at
all.

But...... if you did want to change the recordsource, and it's not working
in the On Open event, you could always open the report in Design mode,
change the recordsource, and then open in Preview mode. But I don't think
that's the best way to go. Better is just to apply your criteria on the fly.

So, post the above info, and we'll go from there.

"Simon" <Sv********@Versatel.nlwrote in message
news:47***********************@news.tele2.nl...

>I had no other code in the main and sub report as the

Me.RecourdSource = "QueryX" in OnOpen event of the
MainReport

And

Me.RecourdSource = "QueryY" in OnOpen event of the SubReport

My coal was to avoid the situation that I need for each filter (query with
selection criteria) a different report.

So I have to change the RecordSource of the main and SubReport by checking
which query has to be filled out as RecordSource. But before I write this
code in the OnOpen of the MainReport I did a test of it works to change
the
RecordSource in the MainReport and SubReport. And it came out that it
works
in the Main Report and it dos not work in the SubReport. And because of
this
I have now 6 the same reports with different selection queries as
RecordSource.

"Neil" <no****@nospam.netschreef in bericht
news:3w*******************@newssvr29.news.prodigy. net...

>I really don't understand then. Thought perhaps you had an older version

of

>Access.

Do you have anything else going on in the Open events in the main report

or

>subreport? If so, then please post that code.

Other than that, I'm not sure why it would be doing that.
"Simon" <Sv********@Versatel.nlwrote in message
news:47***********************@news.tele2.nl...

My version of Access is 2003 (11.6566.6568) SP2

"Neil" <no****@nospam.netschreef in bericht
news:o3******************@newssvr13.news.prodigy.n et...
I don't experience that. I'm using Access 2000, and, changing the
recordsource in the subreport's OnOpen event with the syntax:

Me.Recordsource = "TableOrQueryName"

changes it to that table or query and doesn't produce an error.

What version of Access are you in?
"Simon" <Sv********@Versatel.nlwrote in message
news:47**********************@news.tele2.nl...
If I do so, change the subreport's recordsource in his own

subreport's

recordsource in his own OnOpen event the next message pop's up:
"You can't set the record source property in print preview or after
printing
has started."

And in case I change the query name to a not existing one the

following

message pop's up:
"The record source "NotExistingQuery" specified on this form or

report

does
not exist."
So that means that the sytax is running.

But still a can't change the recordsource of the subreport.

"Neil" <no****@nospam.netschreef in bericht
news:fC*****************@newssvr19.news.prodigy.ne t...
The syntax is correct, but Access won't let you change the

subreport's

>recordsource after it's opened in the main report (was thinking of
changing
other properties of a subreport when I wrote that).

So what you need to do is change the subreport's recordsource

property

from
within its own OnOpen event, rather than the parent report's OnOpen
event.
You should be able to just go:

Me.Recordsource = "adfasdf"

in the subreport's OnOpen event.
"Simon" <Sv********@Versatel.nlwrote in message
news:47***********************@news.tele2.nl...
If I use the syntax:
Reports!MasterReportName!SubReportControlName.Repo rt.Recordsource

=

"asdfadsf"

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

And I have checked the names of the master en sub for report

several

times
and they are correct.
What can by wrong?
"Neil" <no****@nospam.netschreef in bericht
news:QW*****************@newssvr19.news.prodigy.ne t...
I assume you mean RecordSource, not RowSource. The syntax would

be:

>>
Reports!MasterReportName!SubReportControlName.Rep ort.Recordsource

=

>"asdfadsf"

You're actually referring to the "Report" property of the

subreport

control,
and then the RecordSource property of that report.
"Simon" <Sv********@Versatel.nlwrote in message
news:47***********************@news.tele2.nl...
Dear reader,

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

Me.RowSource = "TableOrQueryName"

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

I think it has to be something like this:

Reports.MasterReportName.SubReportName.RowSource =
"TableOrQueryName"

But this is not working.

Is there a working syntax for this?

Tanks for any help.

Kind regards,

Simon