Access 2007 problem printing batch of reports to Bullzip PDF Printer

I'm just getting my feet wet on batch printing to PDF. I started with the sample code that came with the free Bullzip PDF software. It's setup to print a single report. I added the code for the SQL and opening the record set to loop which is pretty straight forward.

The problem I am having is that the function seems to be sending the reports to the PDF printer faster than it can handle. I run into issues where the name of the report is actually different than the content and not all the reports get printed. Some of the names have special characters so I thought that might be effecting the SQL so I changed the file naming to the primary key which is numeric. In the screen shot of the HullerIDs number 2 and 3 got skipped and the file naming even managed to jump from the HullerID to the HullerName.

I added the msgbox right before the line where the file path/name is set:
   .SetValue "output", GetDatabaseFolder & "\out\" & rs!hullername & ".PDF"

as long as I inject a little delay between each report everything comes out as expected.

Since this PDF printer seems to be specifically made for batch printing I'm assuming I must be doing something wrong sending it the reports. As you can see the report is rather simple.Is there something wrong with the way I'm structuring my code?

I don't see a need to set all the printer settings on every print so I tried moving it out of the loop and only setting the path but that didn't have any effect. I'm sure I'm missing something but I'm officially out of things to try. TIA

Public Function PrintReportAsPDF()
   Dim iPdfPrinterIndex As Integer
   Dim sCurrentPrinterName As String
   Dim iCurrentPrinterIndex As Integer
   Dim i As Integer
   Dim sCurrentDir As String
   Dim oPrinterSettings As Object
   Dim oPrinterUtil As Object
   Dim sPrinterName As String

   Dim sDocName As String
   Dim sCriteria As String
   Dim db As Database
   Dim rs As ADODB.Recordset
   Dim sSQL As String

   Set db = CurrentDb
   Set rs = New ADODB.Recordset
   sDocName = "rpt_Auto_Huller"

   sSQL = _
   "SELECT DISTINCT qryLot_Basics.HullerID, qryLot_Basics.HullerName " & _
   "FROM qryLot_Basics " & _
   "WHERE (((qryLot_Basics.HullerName) is Not Null) AND " & _
   "((qryLot_Basics.Date) Between #" & _
   [Forms]![frmAdmin].[tbCYRangeStart] & "# And #" & [Forms]![frmAdmin].[tbCYRangeEnd] & "#)) " & _
   "ORDER BY qryLot_Basics.HullerName;"
   'MsgBox (sSQL)

   rs.Open sSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdText

    Rem -- Create the objects to control the printer settings.
    Rem -- Replace biopdf with bullzip if you have the bullzip printer installed instead
    Rem -- of the biopdf printer.
    Set oPrinterSettings = CreateObject("Bullzip.PdfSettings")
    Set oPrinterUtil = CreateObject("Bullzip.PdfUtil")
    Rem -- Get default printer name
    sPrinterName = oPrinterUtil.DefaultPrintername
    oPrinterSettings.Printername = sPrinterName
    Rem -- Get the directory of the database
    sCurrentDir = GetDatabaseFolder
    Rem -- Find the index of the printer that we want to use
    iPdfPrinterIndex = -1
    iCurrentPrinterIndex = -1
    sCurrentPrinterName = Application.Printer.DeviceName
    For i = 0 To Application.Printers.Count - 1
        If Application.Printers.Item(i).DeviceName = sPrinterName Then
            iPdfPrinterIndex = i
        End If
        If Application.Printers.Item(i).DeviceName = sCurrentPrinterName Then
            iCurrentPrinterIndex = i
        End If
    Rem -- Exit here if the pdf printer was not found
    If iPdfPrinterIndex = -1 Then
        MsgBox "The printer '" & sPrinterName & "' was not found on this computer."
        Exit Function
    End If
    Rem -- Exit here if the current printer was not found
    If iCurrentPrinterIndex = -1 Then
        MsgBox "The current printer '" & sCurrentPrinterName & "' was not found on this computer." & _
            " Without this printer the code will not be able to restore the original printer selection."
        Exit Function
    End If

   With rs
      While Not .EOF
    Rem -- Set the printer
    Application.Printer = Application.Printers(iPdfPrinterIndex)
    Rem -- Configure the PDF printer
    With oPrinterSettings
        Rem -- Set the destination file name of the PDF document
         'MsgBox (rs!hullername & " ~ " & rs!hullerID)
        .SetValue "output", GetDatabaseFolder & "\out\" & rs!hullername & ".PDF"
        Rem -- Control the dialogs when printing
        .SetValue "ConfirmOverwrite", "yes"
        .SetValue "ShowSaveAS", "never"
        .SetValue "ShowSettings", "never"
        Rem -- .SetValue "ShowPDF", "yes"
        .SetValue "ShowPDF", "Never"

        Rem -- Set document properties
        .SetValue "Target", "printer"
        .SetValue "Title", "Access PDF Example"
        .SetValue "Subject", "Report generated at " & Now

        Rem -- Display page thumbs when the document is opened
        Rem -- .SetValue "UseThumbs", "yes"
        .SetValue "UseThumbs", "Never"

        Rem -- Set the zoom factor to 50%
        .SetValue "Zoom", "75"

        Rem -- Place a stamp in the lower right corner
'        .SetValue "WatermarkText", "ACCESS DEMO"
'        .SetValue "WatermarkVerticalPosition", "bottom"
'        .SetValue "WatermarkHorizontalPosition", "right"
'        .SetValue "WatermarkVerticalAdjustment", "3"
'        .SetValue "WatermarkHorizontalAdjustment", "1"
'        .SetValue "WatermarkRotation", "90"
'        .SetValue "WatermarkColor", "#ff0000"
'        .SetValue "WatermarkOutlineWidth", "1"

        Rem -- Write the settings to the runonce.ini file
        .WriteSettings True

    End With

   Rem -- Run the report
   sCriteria = "[HullerID] = " & rs!hullerID
   DoCmd.OpenReport sDocName, acViewNormal, , sCriteria

   End With

End Function

Open in new window

Who is Participating?
Nick67Connect With a Mentor Commented:
MS Access 2007+ has native pdf support.
For 2007, you have to download and install it
Most of what you're doing there then becomes unnecessary and can be replaced with stuff like

    DoCmd.OpenReport stDocName, acPreview
    DoCmd.OutputTo acOutputReport, stDocName, FormatValue, "c:\tempPdf\" & Reports(stDocName).Caption & ".pdf"
    DoCmd.Close acReport, stDocName, acSaveYes
Jeffrey CoachmanMIS LiasonCommented:
I (and many Experts here) use this and I have never had an issue with creating hundreds of PDF's at a time.

<as long as I inject a little delay between each report everything comes out as expected.>
You might also test using: DoEvents
...between the runs

<GetDatabaseFolder & "\out\" & rs!hullername & ".PDF">

If "GetDatabaseFolder" returns a particular long string, or if the folder is located on a distant server, or the Network is slow, then this may also contribute to the inconsistencies...

And just so we are clear, when you say "Send to the PDF printer", do you really mean "convert the report to a pdf", ...or are you actually printing theses PDFs out on paper (Hardcopy)...


Jeffrey CoachmanMIS LiasonCommented:

<access 2007>
...oops missed that...


Raland9966Author Commented:
Thanks, that worked great. I downloaded the save as PDF back when I first got office 2007 but didn't use it so I never installed it again over numerous machine upgrades.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.