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

  • Thread starter Thread starter Dave Corun
  • Start date Start date
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
 
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.
 
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...
 
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...
 
Back
Top