Learn how to a build a cloud-first strategyRegister Now


Routine works fine when run from inside the code but not when called from outside

Posted on 2011-05-04
Medium Priority
Last Modified: 2012-05-11
I have a routine to print Access reports to pdf.  named  Sub PrintAccessReportToPDF_Early()

I got it from  http://sourceforge.net/projects/pdfcreator/

When I run it from inside the sub, it works well (after I built in some code to check whether the file does not already exist and to kill it if it does and this works fine.)

However if I call it from a On Click of a button  Private Sub cmdPdf_Click()   with a call PrintAccessReportToPDF_Early,       then I get a message that "The command or action "Open Report" isn't available now."   This occurs on a line of code that is supposed to print to pdf              
    DoCmd.OpenReport (sReportName)

Can U cast light on this problem.

Why work perfectly when run from inside the sub, but not when called from outside. I include the code.

Sub PrintAccessReportToPDF_Early()
'Author       : Ken Puls ( www.excelguru.ca)
'Macro Purpose: Print to PDF file using PDFCreator
'   (Download from http://sourceforge.net/projects/pdfcreator/ )
'   Designed for early bind, set reference to PDFCreator

'RestoreDefaults 'Restore PDFCreator defaults
MsgBox " in modPrintToPdf"

    Dim pdfjob As PDFCreator.clsPDFCreator
    Dim sPDFName As String
    Dim sPDFPath As String
    Dim sPrinterName As String
    Dim sReportName As String
    Dim sFilePath As String
    Dim lPrinters As Long
    Dim lPrinterCurrent As Long
    Dim lPrinterPDF As Long
    Dim prtDefault As Printer
    Dim bRestart As Boolean

    Set Application.Printer = Application.Printers(lPrinterCurrent)
    '/// Change the report and output file name here! ///
'    sReportName = "rptID100"
    sReportName = "Report1"
    sPDFName = sReportName & ".pdf"
    sPDFPath = Application.CurrentProject.Path & "\Attachments\"
    sFilePath = sPDFPath & sPDFName
    If FileExists(sFilePath) = True Then
'    If FileExists("C:\Wes_Kus\Attachments\Report1.pdf") = True Then
        MsgBox "File does exist"
        VBA.Kill sFilePath ' delete the file.
        MsgBox "file does not exist"
    End If

    MsgBox "Print nou " & sReportName & " na .pdf.   Aan begin van modPrintToPdf. "
    'Resolve index number of printers to allow changing and preserving
    sPrinterName = Application.Printer.DeviceName
    On Error Resume Next
    For lPrinters = 0 To Application.Printers.Count
        Set Application.Printer = Application.Printers(lPrinters)

        Set prtDefault = Application.Printer
        Select Case prtDefault.DeviceName
            Case Is = sPrinterName
                lPrinterCurrent = lPrinters
            Case Is = "PDFCreator"
                lPrinterPDF = lPrinters
            Case Else
                'do nothing
        End Select
    Next lPrinters
    On Error GoTo 0
    'Change the default printer
    Set Application.Printer = Application.Printers(lPrinterPDF)
    Set prtDefault = Application.Printer

    'Start PFF Creator
    Set pdfjob = New PDFCreator.clsPDFCreator
    With pdfjob
        If .cStart("/NoProcessingAtStartup") = False Then
            MsgBox "Can't initialize PDFCreator.", vbCritical + _
                    vbOKOnly, "PrtPDFCreator"
            Exit Sub
        End If
        .cOption("UseAutosave") = 1
        .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = sPDFPath
        .cOption("AutosaveFilename") = sPDFName
        .cOption("AutosaveFormat") = 0    ' 0 = PDF
    End With

    'Print the document to PDF ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
    DoCmd.OpenReport (sReportName)
    'Wait until the print job has entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = 1
    pdfjob.cPrinterStop = False

    'Wait until PDF creator is finished then release the objects
    Do Until pdfjob.cCountOfPrintjobs = 0
        DoEvents    'DoEvents passes control to the operating system.
                    'Control is returned after the operating system has
                    'finished processing the events in its queue and
                    'all keys in the SendKeys queue have been sent.
                    'Die mag 'n potensiele plek vir 'n probleem wees.

    'Reset the (original) default printer and release PDF Creator
    Set Application.Printer = Application.Printers(lPrinterCurrent)
    Set pdfjob = Nothing
    MsgBox "Klaar geprint. Aan die einde van modPrintToPdf. "
End Sub

Sub RestoreDefaults()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Reset the PDFCreator seetings to their default values

    Dim objPDF As New PDFCreator.clsPDFCreator

    With objPDF
        If .cStart("/NoProcessingAtStartup") = False Then
            MsgBox "Can't initialize PDFCreator.", vbCritical + _
                    vbOKOnly, "PrtPDFCreator"
            Exit Sub
        End If
        'Set Values
        .cOption("UseAutosave") = 0
        .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = "\"
        .cOption("AutosaveFilename") = ""
        .cOption("AutosaveFormat") = 0
        .cOption("UseCreationdate") = vbNullString
        .cOption("UseStandardAuthor") = 0
        .cOption("PDFUseSecurity") = 0
        .cOption("PDFUserPass") = 0
        .cOption("PDFUserPassString") = vbNullString
        .cOption("PDFOwnerPass") = 1
        .cOption("PDFOwnerPassString") = vbNullString
        .cOption("PDFEncryptor") = 0
        .cOption("PDFDisallowCopy") = 1
        .cOption("PDFDisallowPrinting") = 0
        .cOption("PDFDisallowModifyContents") = 0
        .cOption("PDFDisallowModifyAnnotations") = 0
        .cOption("PrinterTempPath") = "PDFCreator\"
        'Save Values
    End With
    Set objPDF = Nothing
End Sub

Open in new window

Question by:Fritz Paul
  • 3
  • 2
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35693882
try changing this

DoCmd.OpenReport (sReportName)


DoCmd.OpenReport sReportName

Author Comment

by:Fritz Paul
ID: 35694169
Unfortunately this did not help.

I find that after a restart the process works a few times and then something goes wrong.

I attach the database. It is small and only the printing procedure. I will appreciate if you will have a look.
I use 2003.

Of course when it starts to hook, the PdfCreator does not close and has to be closed through task manager otherwise you get another bad message.
LVL 74

Accepted Solution

Jeffrey Coachman earned 1500 total points
ID: 35695507
Why not contact the author of this code directly?
...They would know more about this issue than any of us...

In any event, ...I use this and have never had any issue with generating PDF's

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.


Author Comment

by:Fritz Paul
ID: 35695678
Thanks you are right.
There just keeps on problems creeping in with this routine. If you scour the internet you come accross so many different issues with this approach, that I finally decided to follow the Lebans approach, which I did not want to follow due to the extra dll's which I had to "carry".
I have now tried the Lebans method. It works and it also works in Access 2010.
Thanks a lot.

Author Closing Comment

by:Fritz Paul
ID: 35695687
I hope I don't come accross this type of problem again.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35698161
Yes you are correct.

The Lebans code does require you to keep track of the dll(s)
But the benefit of it "Just working" is well worth it.



Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question