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

How to change the RowSource in a SubReport

P: n/a
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
Oct 7 '07 #1
Share this Question
Share on Google+
11 Replies


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


Oct 7 '07 #2

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


Oct 7 '07 #3

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


Oct 7 '07 #4

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




Oct 7 '07 #5

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





Oct 8 '07 #6

P: n/a
On Oct 8, 1:00 am, "Simon" <SvanBee...@Versatel.nlwrote:
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
*****************

Oct 8 '07 #7

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




Oct 8 '07 #8

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







Oct 8 '07 #9

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






Oct 9 '07 #10

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









Oct 9 '07 #11

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








Oct 10 '07 #12

This discussion thread is closed

Replies have been disabled for this discussion.