• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

Need a more elegant way to know when the printer finishes

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
  • 2
  • 2
1 Solution
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.
rogerdjrAuthor Commented:
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?

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).
rogerdjrAuthor Commented:
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, _ _


Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now