Link to home
Start Free TrialLog in
Avatar of Fritz Paul
Fritz PaulFlag for South Africa

asked on

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

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.
    Else
        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
        .cClearCache
    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
        DoEvents
    Loop
    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.
    Loop
    pdfjob.cClose

    '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
        .cSaveOptions
    End With
    Set objPDF = Nothing
End Sub

Open in new window

Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

try changing this

DoCmd.OpenReport (sReportName)


to


DoCmd.OpenReport sReportName
Avatar of Fritz Paul

ASKER

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.
 PrintToPdf.mdb
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
I hope I don't come accross this type of problem again.
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.

;-)

Jeff