Change RecordSource for Report; Subreports

L

L.A. Lawyer

Question 1 of 2:

I want to change the recordsource for a report. When I try to change the
recordsource before DoCmd.OpenReport (in PrintPreview mode), Access 2000
reports that there is no such report; when I switch the order, Access
displays an error that I can't change the recordsource after the "printing
has started". Error 2191.

Here are my instructions:
DoCmd.OpenReport "MajorEvents", acViewPreview, , "CaseName ='" &
StrCaseName & "'"
Reports!MajorEvents.RecordSource = "SELECT . . . " (The SQL query
is fine.)

What is the proper approach?

Question No. 2:

Although I am experienced Access user, I have never tried to nest a Report
within another (i.e., a subreport). I want to do this and the subreport
will have a different recordsource. I also want the subreport to follow the
main report, starting on a new page. How do I do this?
 
D

Duane Hookom

You can change the record source of a report using code in the On Open event
of the report. Changing the record source is a bit of an unusual request.

I would recommend basing the report on a saved query. Then use a little DAO
code to change the SQL property of the saved query prior to opening the
report. This also comes in handy for changing the record sources of
subreports.

If you want a subreport to "follow" the main report, place it at the bottom
of the Report Footer section of the main report.
 
M

Marshall Barton

L.A. Lawyer said:
Question 1 of 2:

I want to change the recordsource for a report. When I try to change the
recordsource before DoCmd.OpenReport (in PrintPreview mode), Access 2000
reports that there is no such report; when I switch the order, Access
displays an error that I can't change the recordsource after the "printing
has started". Error 2191.

Here are my instructions:
DoCmd.OpenReport "MajorEvents", acViewPreview, , "CaseName ='" &
StrCaseName & "'"
Reports!MajorEvents.RecordSource = "SELECT . . . " (The SQL query
is fine.)

What is the proper approach?

The only appraoch is to set the report's record source in
the report's Open event procedure.
Question No. 2:

Although I am experienced Access user, I have never tried to nest a Report
within another (i.e., a subreport). I want to do this and the subreport
will have a different recordsource. I also want the subreport to follow the
main report, starting on a new page.

Put the subreport in the main report's Report Footer
section. If you already have stuff in the report footer,
then add a page break control just before the subreport. If
there is noing in the report footer, set its ForceNewPage
property to Before Section.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top