Exporting reports to PDF slow

[Jupie23]

Using the second page of this thread (https://access-programmers.co.uk/forums/showthread.php?t=206372&highlight=outputto) I am able to export a report that is filtered by person and save it under their name and today's date, but it is taking over 5 minutes to export 3 1 page reports. There are two subreports within the main report, and when it is exporting, the popup will show the same person's name a couple of times, like it is doing it more than once. Here is the code I am using:

Private Sub Command74_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qryProductivityProcessorIndividual")
qdf.Parameters(0).Value = Forms!frmReports![Start Date]
qdf.Parameters(1).Value = Forms!frmReports![End Date]
Set rst = qdf.OpenRecordset
Do While Not rst.EOF
    strRptFilter = "[Processor] = " & Chr(34) & rst![Processor] & Chr(34)
    
    DoCmd.OpenReport "rptErrorsbyProcessorIndividual", acPreview
    DoCmd.OutputTo acOutputReport, "rptErrorsbyProcessorIndividual", acFormatPDF, "(deleted)" & rst![Processor] & " - Weekly - " & Format(Date, "mm.dd.yyyy") & ".pdf"
    DoCmd.Close
    DoEvents
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing
End Sub

Any ideas on why it is so slow or what I can do to speed it up? The report itself is also slow to open in print preview, probably 20 seconds. The query seems fine when I run it separately. Any help would be appreciated! Thank you!

 

[CJ_London]

just a thought - the dots in "mm.dd.yyyy" may be confusing the situation by implying a file type. suggest replace with spaces or remove.

if the report takes 20 seconds to open, but the recordsource is OK I would also look for why the report is so slow - perhaps there is heavy formatting going on

 

[Jupie23]

Thank you, I removed the dots to test and it didn't seem to make a difference. But I will keep them out just in case. I just noticed also that when it does the print preview and then closes the report for each person, it's opening the same person's report more than once. Could it be something with how the report is formatted? I have it grouped by person (processor). The report is pretty basic. It has 2 subreports with sums and the main report has a calculated field to calculate the error ratio. I probably should have posted this under reports.

 

[CJ_London]

it's opening the same person's report more than once. Could it be something with how the report is formatted?

I suspect you need to apply your strReportFilter to the openreport command - I don't see where it is used otherwise. But then I would expect you would get the same report multiple times

I would step through the code to find out what the values are, what the report looks like before outputting and seeing which bit takes the time

 

[Jupie23]

Sorry, I'm kind of new to this. Is the openreport command the same thing as the report's On Open event? I have these in there based on the other thread:

Private Sub Report_Close()
strRptFilter = vbNullString
End Sub
 
Private Sub Report_Open(Cancel As Integer)
If Len(strRptFilter) <> 0 Then
     Me.Filter = strRptFilter
     Me.FilterOn = True
End If
End Sub

I also tried Step Into under Debug, but nothing happens. I have not used that before though so it's probably user error.

 

[CJ_London]

No in your first code, add it here.

DoCmd.OpenReport "rptErrorsbyProcessorIndividual", acPreview, ,strRptFilter

applying the filter in the report open event is probably why it is taking so long

 

[CJ_London]

I also tried Step Into under Debug, but nothing happens

put the cursor on the line where you want the code to stop, then with the mouse, select Debug>Toggle Breakpoint - a maroon dot will appear in the grey vertical bar. Or you can just click in the grey vertical bar. The code will stop running at that point and you step through by using the F8 key - or to recommence use the F5 key.

While in break mode you can hover over a variable and see its value or use the immediate window

 

[Jupie23]

I moved the strRptFilter like you said and that did help the speed! The same reports were still opening for the same person multiple times, and I fixed it by making a query for my main report that only had the processor name, and grouped it so each name only showed up once. Then the actual data I put in subreports and linked to the processor name on the main report. Each report only opens once and it's much faster. Thanks for your help!!