Need a more elegant way to know when the printer finishes

Posted on 2011-04-19
Last Modified: 2012-05-11
I am using the follow subroutine to prinf=t a report as a pdf and copy it to a new path and location

Right now it uses the code:

           'Delay to let report run as a pdf
            Delay = 0
            While Delay < 1000000
                Delay = Delay + 0.01

to wait for the print process to stop before copying the file to the new path. Since some reports are two pages and some are 150 pages, the delay needs to vary.

Does anybody have an idea for a more elegant way to set the delay or to wait until the printing is finished?

Sub MultiPdfReports(Optional ReportPath As String, Optional ReportName As String, Optional ReportFilter As String, Optional TempReportNm As String, Optional FinalReportNm As String)

    Dim dbs As Database, strSql As String, rst As Recordset
    Dim strDefaultPrinter As String
    Dim prt As Access.Printer
    Dim strDistillerPrinter As String
    Dim strPrinterName As String
    Dim OldFilePathNm As String
    Dim Delay As Double
    Set dbs = CurrentDb
    STRUSERID = GetNetUserName()
    strSql = "SELECT [0_UserSpecificData].UserId, "
    strSql = strSql & "[0_UserSpecificData].AdobeDocDefaultPath, "
    strSql = strSql & "[0_UserSpecificData].DateofLastLogIn FROM "
    strSql = strSql & "0_UserSpecificData WHERE "
    strSql = strSql & "((([0_UserSpecificData].UserId)="
    strSql = strSql & """" & STRUSERID & """" & "));"

    Set rst = dbs.OpenRecordset(strSql)
    If Not rst.EOF Then
        If IsNull(rst![AdobeDocDefaultPath]) Then
            DefaultPdfPath = InputBox("Enter Default Adobe Print Path", , "C:\Documents and Settings\" & STRUSERID & "\My Documents\")
            With rst
                    ![AdobeDocDefaultPath] = DefaultPdfPath
            End With
            DefaultPdfPath = rst![AdobeDocDefaultPath]
        End If
    End If

'1.  Get the name of the system default printer
            strDefaultPrinter = Application.Printer.DeviceName
'2.  Get the name of the Distiller printer
    'Make Sure the Acrobat Printer is setup to print directly to the printer
            For Each prt In Application.Printers
                strPrinterName = prt.DeviceName
                If InStr(1, strPrinterName, "NowPDF Writer") Then
                    strDistillerPrinter = strPrinterName
                End If
'3.  Redefine the default printer to be the Distiller printer:
            Application.Printer = Application.Printers(strDistillerPrinter)
'4.  Create the PDF report
On Error GoTo 0
            If ReportFilter = "" Then
                DoCmd.OpenReport ReportName
                DoCmd.OpenReport ReportName, acViewNormal, ReportFilter
            End If

           'Delay to let report run as a pdf
            Delay = 0
            While Delay < 1000000
                Delay = Delay + 0.01
'  C.  Copy the PDF report to a new name/email/print/whatever
            OldFilePathNm = Trim(DefaultPdfPath & TempReportNm) & ".pdf"

On Error Resume Next
            Kill ReportPath & FinalReportNm & ".pdf"
            FileCopy OldFilePathNm, ReportPath & FinalReportNm & ".pdf"
On Error GoTo 0
           'Delay while copying
            Delay = 0
            While Delay < 1000000
                Delay = Delay + 0.02

'            DoCmd.OpenReport ReportName

'5.  Reset the default printer to its original setting
            Application.Printer = Application.Printers(strDefaultPrinter)

End Sub
Question by:rogerdjr
    LVL 37

    Expert Comment

    Did you know that Microsoft now offers a free add-in that allows you to save directly to pdfs? It's new as of Office 2007.
    You can do it from the code and it will automatically wait until it's done saving so you can do exactly what you want without any messy code or inelegant hacks at all.

    Author Comment

    great idea thanks - I didn't see a code string example on how to use the app,

    I'll search the web for examples.

    Do you know of a simple string that lets me use this add-in to run the report, with a filter and save it as a pdf with a specified file and path?

    LVL 37

    Accepted Solution

    I always just use 'record macro' to get the code.
    I have Excel, not Access, but it will look something like this:
        ReportObjectGoesHere.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\temp\access2pdftest.pdf", Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    (Note, the code above is just from hitting 'record macro' and saving the sheet as a pdf. Most of those parameters can be left out and are the defaults. I left them in to make it easier to see what options you have).

    Author Closing Comment

    Ended up using this code to print

    strCurrentPath = Application.CurrentProject.Path
    strReport = "rptProductsToReorder"

    strReportFile = strCurrentPath & "\Products To  Reorder.pdf"

       Debug.Print "Report and path: " & strReportFile

       DoCmd.OutputTo objecttype:=acOutputReport, _

          objectname:=strReport, _

          outputformat:=acFormatPDF, _ _


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Most of my previous articles (, Rob the ComputorTutor)  on Office looked at the 2003 version, which was virtually identical to the 2002 and 2000 version, and very similar to their 97 and 95 versions.  However, recently, Microsoft int…
    Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
    The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
    Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

    737 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

    20 Experts available now in Live!

    Get 1:1 Help Now