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

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

I got it from

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 (
'Macro Purpose: Print to PDF file using PDFCreator
'   (Download from )
'   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 (
'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
    LVL 119

    Expert Comment

    by:Rey Obrero
    try changing this

    DoCmd.OpenReport (sReportName)


    DoCmd.OpenReport sReportName

    Author Comment

    by:Fritz Paul
    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

    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


    Author Comment

    by:Fritz Paul
    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
    I hope I don't come accross this type of problem again.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    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.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This script will sweep a range of IP addresses (class c only, and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
    This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now