Printing a 2 page report can take 20 minutes or more

[troutusa]

I have a report in access that when I open it up, it opens quickly as expected. When I try to print preview or print to a printer, the report takes way too long to print. (20 minutes or more before the report prints and access responds to mouse clicks.)

I am wondering if there is a way to find out what is causing the delay. Can I write some code that will show what the report is doing and maybe diagnose why the report takes so long to process?

 

[GPGeorge]

I have a report in access that when I open it up, it opens quickly as expected. When I try to print preview or print to a printer, the report takes way too long to print. (20 minutes or more before the report prints and access responds to mouse clicks.)

I am wondering if there is a way to find out what is causing the delay. Can I write some code that will show what the report is doing and maybe diagnose why the report takes so long to process?

How many records are in the report's record source? How complex is the query that provides records? Does the report involve a lot of calculated controls, or conditional formatting, or lookups? Does it do a lot of grouping or sorting or summarization? Does it involve images or charts?

 

[Gasman]

Just that report?
In Preview, how long does it take to go to last page?

 

[The_Doc_Man]

Access makes a distinction between viewing a report and PREVIEWING a report (with or without intent to print). First question: When you are using some other utility and want to print some document to that same printer, do you see any slowdowns? In particular, if you want to print something via Word, does that take longer than you expect?

 

[GPGeorge]

Along the lines of what Gasman and The_Doc_Man mention, the Print Preview of a report should always be faster when a report goes beyond one page because it involves only the first page in the report in that Preview. If you want to see additional pages in Print Preview, Access then processes and displays them. So, if you go to the last page in Print Preview, so might well see a similar delay.

Another factor that I didn't originally think of is page numbering. If you insert "Page X of Y Pages", where X is the current page and Y is the total number of pages, that forces Access generate the entire report to figure out how many pages will be printed. That alone often causes long delays in printing.

 

[DHookom]

Is there any code in the report? Have you tried setting the default printer to MS PDF?

 

[The_Doc_Man]

GPGeorge brings up a good point. How many pages are in this report?

DHookom ALSO brings up a valid question. Do you have any code in the report section events - particularly the Detail section since it is the section usually called most often?

 

[arnelgp]

is the report based on a Query? if so, does the query opens instantly?

 

[troutusa]

How many records are in the report's record source? How complex is the query that provides records? Does the report involve a lot of calculated controls, or conditional formatting, or lookups? Does it do a lot of grouping or sorting or summarization? Does it involve images or charts?

38 records in data source query. Query runs immediately.
There are 2 unbound controls with calculated data. The data is the full path name, and the version. (I added debug.print to the code with a time stamp for retrieving this data. The data is pulled in immediately according to the start and stop time stamps for both sections of code.)
There is conditional formatting. If the data is in a certain range the background is highlighted and the font bold.
There are 2 sorts without any summary information or header/footer sections.
No charts.

 

[arnelgp]

if there are no VBA involved, better make new report out of your query.

 

[troutusa]

Just that report?
In Preview, how long does it take to go to last page?

The print preview page displays instantly. I can scroll page one, but I can not go to page 2, close the print preview, or print the report. (I use a command button on the report that runs the following:

Private Sub btnPrint_Click()
DoCmd.RunCommand acCmdPrintPreview

End Sub

I just tried using the Ribbon to open the report in Print Preview, and to Print the report. The report preview opened quickly and I was able to go to page 2 immediately. The report printed out a hard copy just as quickly.

The problem seems to be in opening the report through VBA.

 

[GPGeorge]

38 records in data source query. Query runs immediately.
There are 2 unbound controls with calculated data. The data is the full path name, and the version. (I added debug.print to the code with a time stamp for retrieving this data. The data is pulled in immediately according to the start and stop time stamps for both sections of code.)
There is conditional formatting. If the data is in a certain range the background is highlighted and the font bold.
There are 2 sorts without any summary information or header/footer sections.
No charts.

Try creating a copy of the report without the conditional formatting. Compare the two reports. Let us know if that makes a significant difference. Thank you.

 

[troutusa]

Is there any code in the report? Have you tried setting the default printer to MS PDF?

There is some code in the report, but I added "debug.print 'Begin/End [section name]' & Now()" to any code. The timestamps in the immediate window indicated that all code was executed in less than a second.

What I did try was to use the Ribbon commands to open the Print Preview, and to Print the report. The report opened quickly and printed immediately. The problem seems to be associated with using VBA to open the report in Print Preview. Below is the code I use:

Private Sub btnPrint_Click()
DoCmd.RunCommand acCmdPrintPreview

End Sub

 

[troutusa]

if there are no VBA involved, better make new report out of your query.

I just tried using the Ribbon to open the Preview and Print the report. The report opened and printed without delay. The problem is trying to control the report from VBA. I use a command button and the following code:

Private Sub btnPrint_Click()
DoCmd.RunCommand acCmdPrintPreview

End Sub

I am baffled as to why the report works correctly with the Ribbon Commands, but hangs when opened using VBA.

 

[Gasman]

Have you treied a compact and repair after taking a backup.?

 

[Gasman]

I just tried using the Ribbon to open the Preview and Print the report. The report opened and printed without delay. The problem is trying to control the report from VBA. I use a command button and the following code:

Private Sub btnPrint_Click()
DoCmd.RunCommand acCmdPrintPreview

End Sub

I am baffled as to why the report works correctly with the Ribbon Commands, but hangs when opened using VBA.

Why are you not using OpenReport? with preview option?

 

[GPGeorge]

Where is that command button located? I'm reasonably sure that line of code by itself would only work on a report in Report View. Invoked from a form, it would launch that form in Print Preview. And that could be slower than an equivalent report. Just not 20 minutes slower, I think.

I suspect we still need more context and details to fully understand the problem.

 

[arnelgp]

is the report open in Report view first?
if so, can you close it first before viewing in print preview.
see this demo, open Sheet1 report in report view.

 

[LarryE]

I just tried using the Ribbon to open the Preview and Print the report. The report opened and printed without delay. The problem is trying to control the report from VBA. I use a command button and the following code:

Private Sub btnPrint_Click()
DoCmd.RunCommand acCmdPrintPreview

End Sub

I am baffled as to why the report works correctly with the Ribbon Commands, but hangs when opened using VBA.

It's because you are not opening the report before sending the PrintPreview command so ACCESS has no idea what you want to print.
Use:

DoCmd.OpenReport "Your Report Name", acViewPreview, , , acWindowNormal

to open the report in PrintPreview mode.