Solved Exporting Report to PDF Error (1 Viewer)

Juett

Registered User.
Local time
Today, 13:09
Joined
Jul 16, 2019
Messages
49
Hi everyone,

I have a peace of VBA code that used to work, but now doesn't. Here is the code:


DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "DT14_IQOQdoc", acViewPreview, WhereCondition:="[Doc ID]=" & Me![Doc ID]
DoCmd.OutputTo acOutputReport, "DT14_IQOQdoc", acFormatPDF, CurrentProject.Path & "\Current Reports\" & Me![CustDoc] & " - " & Me![Document Type] & " - " & Me![DocDate] & " - " & Me![Bath Model] & " - " & Me![Bath SN] & ".pdf"
DoCmd.Close acReport, "DT14_IQOQdoc"

The issue is this - when the code is run (via a button), the record is saved, the report opens in print preview, filters to the correct record and is complete and correct, but it will not then save as a PDF. The printing dialogue window appears, and the correct directory and file name are ready to go...but it then immediately disappears and doesn't output the PDF. The report then remains open in print preview mode and doesn't close. I've checked individual parts of the code and each bit works. Form what I can tell, the issue arises between the report opening in print preview and then starting the output to PDF. I get no error messages, and as I said, the report opens correctly but then the whole process seems to just fail as soon as the output to PDF tries to begin.

Interestingly, if I manually open the report and print or export to PDF - this works fine.

Another interesting thing - if I alter the code to open the report and don't ask to filter a specific record, it works too and outputs a massive report with all records.

The report in question is 25 pages long, and contains a lot of individual points of data (300-400) from across 4-5 tables.

I also have a other similar reports that have less data points (200-300) but uses the exact same layout and pages etc. and these DO export to PDF when the code is run.

This code used to always work. But now doesn't and I can't seem to pinpoint the issue. Could this be a memory problem? Is the code running too quickly or has Access not got enough memory to output the larger reports to pdf in this way?

Any ideas are warmly welcome as to what is causing this!
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:09
Joined
Sep 21, 2011
Messages
10,546
Put a breakpoint on the output line, copy that line and paste to immediate window. Does it work there?
 

Juett

Registered User.
Local time
Today, 13:09
Joined
Jul 16, 2019
Messages
49
Thanks Gasman,

This threw up an error "cannot open anymore databases" ...... After looking around I noticed that aside from the active form/record with the Report to PDF button on it, I had also got another form open behind this which I use to open/access the said buttoned form......I closed this one down and now the report seems to render. I guess I was maxing access out?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:09
Joined
Oct 29, 2018
Messages
18,972
The printing dialogue window appears,
I don't recall this as a standard behavior. I thought if you provide all the arguments to the OutputTo command, then you shouldn't see the printer dialog window.
 

Juett

Registered User.
Local time
Today, 13:09
Joined
Jul 16, 2019
Messages
49
Hmmm..... this has always been the case whenever we print anything in Access to PDF. A 'Printing' window appears saying 'Now outputting to the destination etc....' and whizzes through each report page, then closes once the Pdf has been created. This is Access with Office 365. Thanks very much for your help. I think this is solved.
 

SHANEMAC51

Active member
Local time
Today, 15:09
Joined
Jan 28, 2022
Messages
288
Hi everyone,

I have a peace of VBA code that used to work, but now doesn't. Here is the code:


DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "DT14_IQOQdoc", acViewPreview, WhereCondition:="[Doc ID]=" & Me![Doc ID]
DoCmd.OutputTo acOutputReport, "DT14_IQOQdoc", acFormatPDF, CurrentProject.Path & "\Current Reports\" & Me![CustDoc] & " - " & Me![Document Type] & " - " & Me![DocDate] & " - " & Me![Bath Model] & " - " & Me![Bath SN] & ".pdf"
DoCmd.Close acReport, "DT14_IQOQdoc"

The printing dialogue window appears, and the correct directory and file name are ready to go...but it then immediately disappears and doesn't output the PDF. The report then remains open in print preview mode and doesn't close. I've checked individual parts of the code and each bit works. Form what I can tell, the issue arises between the report opening in print preview and then starting the output to PDF. I get no error messages, and as I said, the report opens correctly but then the whole process seems to just fail as soon as the output to PDF tries to begin.

Interestingly, if I manually open the report and print or export to PDF - this works fine.
Code:
Function macro21a()
    Dim sname, spath, srpt
    Dim rpt As Report
    Dim cnt As Control
    Dim s1, s2
    On Error Resume Next
    spath = "c:\temp"
    MkDir spath             ''create catalog
    spath = spath & "\" & Format(Date, "yyyy-mm-dd_")
    MkDir spath             ''create catalog
    Do While True
    s1 = InputBox("last name(a,b,c,*)", , "a")
    If s1 = "*" Then
    Exit Function
    End If
    srpt = "Hollywood Park Results"   '''name report
    DoCmd.OpenReport srpt, acViewReport, "", "[Horse Name]  like '[" & s1 & "]*'", acHidden
    Set rpt = Reports(srpt)
            For Each cnt In rpt.Controls  ''otlad
            Debug.Print cnt.Name, cnt.Value
            Next cnt
    sname = spath & "\" & srpt & "_" & rpt.[Horse Name] & ".pdf"
            Debug.Print sname  ''otlad
    DoCmd.OutputTo acOutputReport, rpt.Name, "PDFFormat(*.pdf)", sname, False, "", , acExportQualityPrint
    DoCmd.Close acReport, rpt.Name
    Loop
End Function
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:09
Joined
Oct 29, 2018
Messages
18,972
Hmmm..... this has always been the case whenever we print anything in Access to PDF. A 'Printing' window appears saying 'Now outputting to the destination etc....' and whizzes through each report page, then closes once the Pdf has been created. This is Access with Office 365. Thanks very much for your help. I think this is solved.
Thanks for the additional information. I just don't remember this is the case when using the OutputTo method.
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:09
Joined
Sep 21, 2011
Messages
10,546
I got 'Now outputting' window with a title of Printing when I tried from the immediate window.?
 

SHANEMAC51

Active member
Local time
Today, 15:09
Joined
Jan 28, 2022
Messages
288
Hi everyone,
I have a peace of VBA code that used to work, but now doesn't. Here is the code:
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "DT14_IQOQdoc", acViewPreview, WhereCondition:="[Doc ID]=" & Me![Doc ID]
DoCmd.OutputTo acOutputReport, "DT14_IQOQdoc", acFormatPDF, CurrentProject.Path & "\Current Reports\" & Me![CustDoc] & " - " & Me![Document Type] & " - " & Me![DocDate] & " - " & Me![Bath Model] & " - " & Me![Bath SN] & ".pdf"
DoCmd.Close acReport, "DT14_IQOQdoc"
once it was a normal form with a data source, controls, a start button and error handling
' but then the button ran away to another form, apparently the handler disappeared
' or part of the controls disappeared
' but without an error handler, it's not visible
'the code is not working correctly
Code:
private sub button1_click()
on error goto button1_click_err

DoCmd.RunCommand acCmdSaveRecord
'контролы c формы'''''''''''
'Me![Doc ID] c формы
'Me![CustDoc] , Me![Document Type] , Me![DocDate] , Me![Bath Model] , Me![Bath SN]
''''''''''''''''''''''''''''
DoCmd.OpenReport "DT14_IQOQdoc", acViewPreview, WhereCondition:="[Doc ID]=" & Me![Doc ID]
DoCmd.OutputTo acOutputReport, "DT14_IQOQdoc", acFormatPDF, CurrentProject.Path & "\Current Reports\" & Me![CustDoc] & " - " & Me![Document Type] & " - " & Me![DocDate] & " - " & Me![Bath Model] & " - " & Me![Bath SN] & ".pdf"
DoCmd.Close acReport, "DT14_IQOQdoc"
exit sub
button1_click_err:
msgbox err.number & " " & err.description
end sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:09
Joined
Sep 21, 2011
Messages
10,546
Thanks Gasman,

This threw up an error "cannot open anymore databases" ...... After looking around I noticed that aside from the active form/record with the Report to PDF button on it, I had also got another form open behind this which I use to open/access the said buttoned form......I closed this one down and now the report seems to render. I guess I was maxing access out?
You might be also suffering from this issue?
 

Users who are viewing this thread

Top Bottom