How to print an Access Report multiple times, w/ different headers

D

Dave Corun

Found a cool Access reporting trick, and decided to help everyone out.

This is how to print an Access report multiple times, but w/o using the
DoCmd.PrintOut command. This will allow you to see the results in a print
preview mode.

1) Create a temp table (Mine is ReportTemp)
2) Create a query for the actual source of the report
3) Use the code below to launch the report

ReportTemp Table:
ReportTempID - Autonumber
ReportCopy - Text (What prints on the extra pages)
InvoiceID - To tie it together
------------------

Report's RecordSource:
SELECT qryInvoiceReport.*, ReportTemp.ReportCopy FROM ReportTemp RIGHT JOIN
qryInvoiceReport ON ReportTemp.InvoiceID=qryInvoiceReport.InvoiceID
------------------

The Report's Grouping Levels:
http://www.greatjustice.com/lj/060104report.jpg

------------------
The Code:
' Open the invoice report in print preview mode
DBEngine(0)(0).Execute "DELETE FROM ReportTemp"

' First Page
DBEngine(0)(0).Execute "INSERT INTO ReportTemp (ReportCopy, InvoiceID)
VALUES (" & _
"""""," & Me.InvoiceID & ")"

' Second Page - YELLOW COPY
DBEngine(0)(0).Execute "INSERT INTO ReportTemp (ReportCopy, InvoiceID)
VALUES (" & _
"""" & "YELLOW COPY" & """," & Me.InvoiceID & ")"

' Third Page - PINK COPY
DBEngine(0)(0).Execute "INSERT INTO ReportTemp (ReportCopy, InvoiceID)
VALUES (" & _
"""" & "PINK COPY" & """," & Me.InvoiceID & ")"

DoCmd.OpenReport "Invoices", acViewPreview, , "InvoiceID = " & Me.InvoiceID

------------------

The result (in this example) is the same report, printed 3 times, w/ the
words "YELLOW COPY" and "PINK COPY" on the 2nd and 3rd page.


// Dave Corun
 
D

Duane Hookom

Thanks for the tip. You might not need to create a temporary table. Consider
creating a single table [tblCopies] with fields [CopyNum] and [CopyTitle]
and records
1 "Original - White"
2 "Customer - Yellow"
3 "File - Pink"
Then add this table to your report's record source without any joins. Drop
the two fields to the query gird. This will create three copies of each
record with your copy titles.
 
D

Dave Corun

Correct, that creates a cartesian join on the tables.

The report I was developing had several complex joins though, so I chose to
nest the queries. I think it's a little easier to work with.

Thanks for reading!

// Dave



Duane Hookom said:
Thanks for the tip. You might not need to create a temporary table. Consider
creating a single table [tblCopies] with fields [CopyNum] and [CopyTitle]
and records
1 "Original - White"
2 "Customer - Yellow"
3 "File - Pink"
Then add this table to your report's record source without any joins. Drop
the two fields to the query gird. This will create three copies of each
record with your copy titles.

--
Duane Hookom
MS Access MVP


Dave Corun said:
Found a cool Access reporting trick, and decided to help everyone out.

This is how to print an Access report multiple times, but w/o using the
DoCmd.PrintOut command. This will allow you to see the results in a print
preview mode.

1) Create a temp table (Mine is ReportTemp)
2) Create a query for the actual source of the report
3) Use the code below to launch the report

ReportTemp Table:
ReportTempID - Autonumber
ReportCopy - Text (What prints on the extra pages)
InvoiceID - To tie it together
------------------

Report's RecordSource:
SELECT qryInvoiceReport.*, ReportTemp.ReportCopy FROM ReportTemp RIGHT JOIN
qryInvoiceReport ON ReportTemp.InvoiceID=qryInvoiceReport.InvoiceID
------------------

The Report's Grouping Levels:
http://www.greatjustice.com/lj/060104report.jpg

------------------
The Code:
' Open the invoice report in print preview mode
DBEngine(0)(0).Execute "DELETE FROM ReportTemp"

' First Page
DBEngine(0)(0).Execute "INSERT INTO ReportTemp (ReportCopy, InvoiceID)
VALUES (" & _
"""""," & Me.InvoiceID & ")"

' Second Page - YELLOW COPY
DBEngine(0)(0).Execute "INSERT INTO ReportTemp (ReportCopy, InvoiceID)
VALUES (" & _
"""" & "YELLOW COPY" & """," & Me.InvoiceID & ")"

' Third Page - PINK COPY
DBEngine(0)(0).Execute "INSERT INTO ReportTemp (ReportCopy, InvoiceID)
VALUES (" & _
"""" & "PINK COPY" & """," & Me.InvoiceID & ")"

DoCmd.OpenReport "Invoices", acViewPreview, , "InvoiceID = " & Me.InvoiceID

------------------

The result (in this example) is the same report, printed 3 times, w/ the
words "YELLOW COPY" and "PINK COPY" on the 2nd and 3rd page.


// Dave Corun
Click to expand...
Click to expand...
 
D

Duane Hookom

Either method beats running the same report three times.

--
Duane Hookom
MS Access MVP


Dave Corun said:
Correct, that creates a cartesian join on the tables.

The report I was developing had several complex joins though, so I chose to
nest the queries. I think it's a little easier to work with.

Thanks for reading!

// Dave



Duane Hookom said:
Thanks for the tip. You might not need to create a temporary table. Consider
creating a single table [tblCopies] with fields [CopyNum] and [CopyTitle]
and records
1 "Original - White"
2 "Customer - Yellow"
3 "File - Pink"
Then add this table to your report's record source without any joins. Drop
the two fields to the query gird. This will create three copies of each
record with your copy titles.

--
Duane Hookom
MS Access MVP


Dave Corun said:
Found a cool Access reporting trick, and decided to help everyone out.

This is how to print an Access report multiple times, but w/o using the
DoCmd.PrintOut command. This will allow you to see the results in a print
preview mode.

1) Create a temp table (Mine is ReportTemp)
2) Create a query for the actual source of the report
3) Use the code below to launch the report

ReportTemp Table:
ReportTempID - Autonumber
ReportCopy - Text (What prints on the extra pages)
InvoiceID - To tie it together
------------------

Report's RecordSource:
SELECT qryInvoiceReport.*, ReportTemp.ReportCopy FROM ReportTemp RIGHT JOIN
qryInvoiceReport ON ReportTemp.InvoiceID=qryInvoiceReport.InvoiceID
------------------

The Report's Grouping Levels:
http://www.greatjustice.com/lj/060104report.jpg

------------------
The Code:
' Open the invoice report in print preview mode
DBEngine(0)(0).Execute "DELETE FROM ReportTemp"

' First Page
DBEngine(0)(0).Execute "INSERT INTO ReportTemp (ReportCopy, InvoiceID)
VALUES (" & _
"""""," & Me.InvoiceID & ")"

' Second Page - YELLOW COPY
DBEngine(0)(0).Execute "INSERT INTO ReportTemp (ReportCopy, InvoiceID)
VALUES (" & _
"""" & "YELLOW COPY" & """," & Me.InvoiceID & ")"

' Third Page - PINK COPY
DBEngine(0)(0).Execute "INSERT INTO ReportTemp (ReportCopy, InvoiceID)
VALUES (" & _
"""" & "PINK COPY" & """," & Me.InvoiceID & ")"

DoCmd.OpenReport "Invoices", acViewPreview, , "InvoiceID = " & Me.InvoiceID

------------------

The result (in this example) is the same report, printed 3 times, w/ the
words "YELLOW COPY" and "PINK COPY" on the 2nd and 3rd page.


// Dave Corun
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

How to programatically add an existing report to a report (as sub) 1
dynamically adding pictures to an Access Report 3
How do I format an Access report to print data on 1/3 page 2
FWT Newsletter - Weekly - March 22, 2004 3

Top