Change Report Control Source

  • Thread starter Thread starter Beth
  • Start date Start date
B

Beth

I need 3 reports that look identical, but they come from different queries.
Instead of creating 3 identical reports, but with different control sources,
I would like to create the report once and then use macros, or code, from a
menu to change the control source.
The report is called rptLayout and the 3 queries are qryTop10, qryGraduated,
and qryInactive.

As further explanation on the main menu, if the user clicks the "Top10"
button, it will open rptLayout and set the control source to qryTop10. If
they close the report and click the menu button titled "Graduated", it
should open the report rptLayout and set the control source to qryGraduated.

Thank you for your help.
Beth
 
Save the report with nothing in its RecordSource property.
Then use the Open event procedure of the report to assign the RecordSource,
based on the value in your form.

This kind of thing:

Private Sub Report_Open(Cancel As Integer)
Select Case Forms![MyForm]![MyOptionGroup]
Case 1
Me.RecordSource = "qryTop10"
Case 2
Me.RecordSource = "qryGraduated"
Case 3
...
Case Else
MsgBox "Huh?"
End Select
End Sub
 
"Beth" wrote
I need 3 reports that look identical, but
they come from different queries.
Instead of creating 3 identical reports,
but with different control sources,
I would like to create the report once
and then use macros, or code, from a
menu to change the control source.
The report is called rptLayout and the
3 queries are qryTop10, qryGraduated,
and qryInactive.

As further explanation on the main menu,
if the user clicks the "Top10" button, it will
open rptLayout and set the control source
to qryTop10. If they close the report and
click the menu button titled "Graduated", it
should open the report rptLayout and set
the control source to qryGraduated.
Click to expand...

I hope you have identical Field names in the three Queries, otherwise this
becomes more complicated. The Query (or Table) on which a Report is based is
the Report's RecordSource; ControlSource refers to the Field in the
RecordSource, or calculation, to be displayed in a Control on the Report.

You can change the RecordSource of a Report in the Report's Open Event. Code
in the OpenEvent can examine a Control on the Form used to Open it, provided
that Form is still open. That Control can even be "invisible" so it is not
obvious to the user. If you create a Control on the Form called "txtQuery"
and set it to the name of the Query in the Click event of the Command
Button, you can refer to it from the Report by

Me.RecordSource = Forms!YourFormsName!txtQuery

Of course, you'd want to validate that there is _some_ non-null, non-blank
data in "txtQuery".

You can't "pass the information in" from outside, unless you open the Report
in Design View, change it, save it, Close the Report in Design View, and
Open it again in Preview. That's 'way too much hassle for me.

Or, you can pass the name of the Query in the OpenArgs argument of the
DoCmd.OpenReport, if you are using Access 2002 or 2003 (that's when OpenArgs
was added for Reports, though it had been available for Forms for many
versions). In the Open event, you'd have something like:

If Len(Me.OpenArgs)<>0 Then
Me.RecordSource = Me.OpenArgs
Else
MsgBox "No Record Source chosen"
Cancel = True
End If


Larry Linson
Microsoft Access MVP
 
I put this code in and the report runs, but it isn't giving the right
output. It is filtering for the right entries based on the query, but the
sort order is not right.
I verified that the queries are sorting correctly, but that sort does not
carry over to the report.
All the queries start with the same 2 sorts and then add different levels of
sorting after that. I was hoping to use the sorting and grouping window in
the report to define the first 2 sorts so that I can easily set the grouping
headers, footers, etc and then have the query define the remain sort
options.
Will that not work?
If not, how do I change the sort on the report for each query?
Thank you,
Beth



Allen Browne said:
Save the report with nothing in its RecordSource property.
Then use the Open event procedure of the report to assign the RecordSource,
based on the value in your form.

This kind of thing:

Private Sub Report_Open(Cancel As Integer)
Select Case Forms![MyForm]![MyOptionGroup]
Case 1
Me.RecordSource = "qryTop10"
Case 2
Me.RecordSource = "qryGraduated"
Case 3
...
Case Else
MsgBox "Huh?"
End Select
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Beth said:
I need 3 reports that look identical, but they come from different queries.
Instead of creating 3 identical reports, but with different control
sources,
I would like to create the report once and then use macros, or code, from
a
menu to change the control source.
The report is called rptLayout and the 3 queries are qryTop10,
qryGraduated,
and qryInactive.

As further explanation on the main menu, if the user clicks the "Top10"
button, it will open rptLayout and set the control source to qryTop10. If
they close the report and click the menu button titled "Graduated", it
should open the report rptLayout and set the control source to
qryGraduated.

Thank you for your help.
Beth
Click to expand...
Click to expand...
 
Beth,

Reports ignore the query sorting and use the grouping /
sorting options within the report. If the three reports
are all sorted on the same fields you can design view your
report and set the sorting / grouping as you want it and
save the report. If you use different sorting in the three
reports it gets messy. I would just copy and paste the
report alter the recordsource and sorting for each report
and call one of the now three reports.

Terry
-----Original Message-----
I put this code in and the report runs, but it isn't giving the right
output. It is filtering for the right entries based on the query, but the
sort order is not right.
I verified that the queries are sorting correctly, but that sort does not
carry over to the report.
All the queries start with the same 2 sorts and then add different levels of
sorting after that. I was hoping to use the sorting and grouping window in
the report to define the first 2 sorts so that I can easily set the grouping
headers, footers, etc and then have the query define the remain sort
options.
Will that not work?
If not, how do I change the sort on the report for each query?
Thank you,
Beth



Save the report with nothing in its RecordSource property.
Then use the Open event procedure of the report to
Click to expand...
assign the
RecordSource,
based on the value in your form.

This kind of thing:

Private Sub Report_Open(Cancel As Integer)
Select Case Forms![MyForm]![MyOptionGroup]
Case 1
Me.RecordSource = "qryTop10"
Case 2
Me.RecordSource = "qryGraduated"
Case 3
...
Case Else
MsgBox "Huh?"
End Select
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need 3 reports that look identical, but they come
Click to expand...
Click to expand...
from different
queries. macros, or code,
from source to qryTop10.
If

.
Click to expand...
 
I was afraid the reports did ignore the query sorts. The reports are
exactly identical in format and fields, but they filter and sort
differently. I currently have it set as 3 separate reports as you said, but
it is causing problems.
One is maintenance because I have to change things in 3 reports if I want to
change any part of it.
Second, the reports have graphics in the header so having 3 of the same is
blowing up the size of the database.

How can I customize the sorting and grouping in the report through the on
open command? I can hard code the first 2 sorts/groups in the report as
they are constant. I would just need to change the rest of the sorts.

I really appreciate your input.

Beth

Terry said:
Beth,

Reports ignore the query sorting and use the grouping /
sorting options within the report. If the three reports
are all sorted on the same fields you can design view your
report and set the sorting / grouping as you want it and
save the report. If you use different sorting in the three
reports it gets messy. I would just copy and paste the
report alter the recordsource and sorting for each report
and call one of the now three reports.

Terry
-----Original Message-----
I put this code in and the report runs, but it isn't giving the right
output. It is filtering for the right entries based on the query, but the
sort order is not right.
I verified that the queries are sorting correctly, but that sort does not
carry over to the report.
All the queries start with the same 2 sorts and then add different levels of
sorting after that. I was hoping to use the sorting and grouping window in
the report to define the first 2 sorts so that I can easily set the grouping
headers, footers, etc and then have the query define the remain sort
options.
Will that not work?
If not, how do I change the sort on the report for each query?
Thank you,
Beth



Save the report with nothing in its RecordSource property.
Then use the Open event procedure of the report to
Click to expand...
assign the
RecordSource,
based on the value in your form.

This kind of thing:

Private Sub Report_Open(Cancel As Integer)
Select Case Forms![MyForm]![MyOptionGroup]
Case 1
Me.RecordSource = "qryTop10"
Case 2
Me.RecordSource = "qryGraduated"
Case 3
...
Case Else
MsgBox "Huh?"
End Select
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need 3 reports that look identical, but they come
Click to expand...
from different
queries.
Instead of creating 3 identical reports, but with different control
sources,
I would like to create the report once and then use
Click to expand...
macros, or code,
from
a
menu to change the control source.
The report is called rptLayout and the 3 queries are qryTop10,
qryGraduated,
and qryInactive.

As further explanation on the main menu, if the user clicks the "Top10"
button, it will open rptLayout and set the control
Click to expand...
source to qryTop10.
If
they close the report and click the menu button titled "Graduated", it
should open the report rptLayout and set the control source to
qryGraduated.

Thank you for your help.
Beth
Click to expand...


.
Click to expand...
Click to expand...
 
In Report_Open, after assigning the RecordSource of the report, assign the
ControlSource of the GroupLevel so that it sorts correctly.

Details and example in this link:
Sorting Records in a Report at run-time
at:
http://members.iinet.net.au/~allenbrowne/ser-33.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Beth said:
I was afraid the reports did ignore the query sorts. The reports are
exactly identical in format and fields, but they filter and sort
differently. I currently have it set as 3 separate reports as you said,
but
it is causing problems.
One is maintenance because I have to change things in 3 reports if I want
to
change any part of it.
Second, the reports have graphics in the header so having 3 of the same is
blowing up the size of the database.

How can I customize the sorting and grouping in the report through the on
open command? I can hard code the first 2 sorts/groups in the report as
they are constant. I would just need to change the rest of the sorts.

I really appreciate your input.

Beth

Terry said:
Beth,

Reports ignore the query sorting and use the grouping /
sorting options within the report. If the three reports
are all sorted on the same fields you can design view your
report and set the sorting / grouping as you want it and
save the report. If you use different sorting in the three
reports it gets messy. I would just copy and paste the
report alter the recordsource and sorting for each report
and call one of the now three reports.

Terry
-----Original Message-----
I put this code in and the report runs, but it isn't giving the right
output. It is filtering for the right entries based on the query, but the
sort order is not right.
I verified that the queries are sorting correctly, but that sort does not
carry over to the report.
All the queries start with the same 2 sorts and then add different levels of
sorting after that. I was hoping to use the sorting and grouping window in
the report to define the first 2 sorts so that I can easily set the grouping
headers, footers, etc and then have the query define the remain sort
options.
Will that not work?
If not, how do I change the sort on the report for each query?
Thank you,
Beth



Save the report with nothing in its RecordSource property.
Then use the Open event procedure of the report to assign the
RecordSource,
based on the value in your form.

This kind of thing:

Private Sub Report_Open(Cancel As Integer)
Select Case Forms![MyForm]![MyOptionGroup]
Case 1
Me.RecordSource = "qryTop10"
Case 2
Me.RecordSource = "qryGraduated"
Case 3
...
Case Else
MsgBox "Huh?"
End Select
End Sub

I need 3 reports that look identical, but they come from different
queries.
Instead of creating 3 identical reports, but with different control
sources,
I would like to create the report once and then use macros, or code,
from
a
menu to change the control source.
The report is called rptLayout and the 3 queries are qryTop10,
qryGraduated,
and qryInactive.

As further explanation on the main menu, if the user clicks the "Top10"
button, it will open rptLayout and set the control source to qryTop10.
If
they close the report and click the menu button titled "Graduated", it
should open the report rptLayout and set the control source to
qryGraduated.

Thank you for your help.
Beth
Click to expand...
Click to expand...
Click to expand...
 
Beth,

What Allen said ......

As to the graphics and size issues. I generally create a
subreport with the header information (company name,
address, logo, phone numbers etc) on it and re-use this
subreport on all reports requiring header information.

Best of luck,

Terry
-----Original Message-----
I was afraid the reports did ignore the query sorts. The reports are
exactly identical in format and fields, but they filter and sort
differently. I currently have it set as 3 separate reports as you said, but
it is causing problems.
One is maintenance because I have to change things in 3 reports if I want to
change any part of it.
Second, the reports have graphics in the header so having 3 of the same is
blowing up the size of the database.

How can I customize the sorting and grouping in the report through the on
open command? I can hard code the first 2 sorts/groups in the report as
they are constant. I would just need to change the rest of the sorts.

I really appreciate your input.

Beth

Beth,

Reports ignore the query sorting and use the grouping /
sorting options within the report. If the three reports
are all sorted on the same fields you can design view your
report and set the sorting / grouping as you want it and
save the report. If you use different sorting in the three
reports it gets messy. I would just copy and paste the
report alter the recordsource and sorting for each report
and call one of the now three reports.

Terry
-----Original Message-----
I put this code in and the report runs, but it isn't giving the right
output. It is filtering for the right entries based on the query, but the
sort order is not right.
I verified that the queries are sorting correctly, but that sort does not
carry over to the report.
All the queries start with the same 2 sorts and then
Click to expand...
add
different levels of
sorting after that. I was hoping to use the sorting
Click to expand...
and
grouping window in
the report to define the first 2 sorts so that I can easily set the grouping
headers, footers, etc and then have the query define
Click to expand...
the
remain sort
options.
Will that not work?
If not, how do I change the sort on the report for each query?
Thank you,
Beth



Save the report with nothing in its RecordSource property.
Then use the Open event procedure of the report to assign the
RecordSource,
based on the value in your form.

This kind of thing:

Private Sub Report_Open(Cancel As Integer)
Select Case Forms![MyForm]![MyOptionGroup]
Case 1
Me.RecordSource = "qryTop10"
Case 2
Me.RecordSource = "qryGraduated"
Case 3
...
Case Else
MsgBox "Huh?"
End Select
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

[email protected]...
I need 3 reports that look identical, but they come from different
queries.
Instead of creating 3 identical reports, but with different control
sources,
I would like to create the report once and then use macros, or code,
from
a
menu to change the control source.
The report is called rptLayout and the 3 queries
Click to expand...
are
qryTop10,
qryGraduated,
and qryInactive.

As further explanation on the main menu, if the
Click to expand...
user
clicks the "Top10"
button, it will open rptLayout and set the control source to qryTop10.
If
they close the report and click the menu button titled "Graduated", it
should open the report rptLayout and set the
Click to expand...
control
source to
qryGraduated.

Thank you for your help.
Beth




.
Click to expand...
Click to expand...


.
Click to expand...
 
Thanks to both of you for the help. I think I have it working now.
Beth
Terry said:
Beth,

What Allen said ......

As to the graphics and size issues. I generally create a
subreport with the header information (company name,
address, logo, phone numbers etc) on it and re-use this
subreport on all reports requiring header information.

Best of luck,

Terry
-----Original Message-----
I was afraid the reports did ignore the query sorts. The reports are
exactly identical in format and fields, but they filter and sort
differently. I currently have it set as 3 separate reports as you said, but
it is causing problems.
One is maintenance because I have to change things in 3 reports if I want to
change any part of it.
Second, the reports have graphics in the header so having 3 of the same is
blowing up the size of the database.

How can I customize the sorting and grouping in the report through the on
open command? I can hard code the first 2 sorts/groups in the report as
they are constant. I would just need to change the rest of the sorts.

I really appreciate your input.

Beth

Beth,

Reports ignore the query sorting and use the grouping /
sorting options within the report. If the three reports
are all sorted on the same fields you can design view your
report and set the sorting / grouping as you want it and
save the report. If you use different sorting in the three
reports it gets messy. I would just copy and paste the
report alter the recordsource and sorting for each report
and call one of the now three reports.

Terry

-----Original Message-----
I put this code in and the report runs, but it isn't
giving the right
output. It is filtering for the right entries based on
the query, but the
sort order is not right.
I verified that the queries are sorting correctly, but
that sort does not
carry over to the report.
All the queries start with the same 2 sorts and then add
different levels of
sorting after that. I was hoping to use the sorting and
grouping window in
the report to define the first 2 sorts so that I can
easily set the grouping
headers, footers, etc and then have the query define the
remain sort
options.
Will that not work?
If not, how do I change the sort on the report for each
query?
Thank you,
Beth



message
Save the report with nothing in its RecordSource
property.
Then use the Open event procedure of the report to
assign the
RecordSource,
based on the value in your form.

This kind of thing:

Private Sub Report_Open(Cancel As Integer)
Select Case Forms![MyForm]![MyOptionGroup]
Case 1
Me.RecordSource = "qryTop10"
Case 2
Me.RecordSource = "qryGraduated"
Case 3
...
Case Else
MsgBox "Huh?"
End Select
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

message
[email protected]...
I need 3 reports that look identical, but they come
from different
queries.
Instead of creating 3 identical reports, but with
different control
sources,
I would like to create the report once and then use
macros, or code,
from
a
menu to change the control source.
The report is called rptLayout and the 3 queries are
qryTop10,
qryGraduated,
and qryInactive.

As further explanation on the main menu, if the user
clicks the "Top10"
button, it will open rptLayout and set the control
source to qryTop10.
If
they close the report and click the menu button
titled "Graduated", it
should open the report rptLayout and set the control
source to
qryGraduated.

Thank you for your help.
Beth




.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top