[Last Call] Learn how to a build a cloud-first strategyRegister Now


Access 2007 problem printing batch of reports to Bullzip PDF Printer

Posted on 2011-09-22
Medium Priority
Last Modified: 2012-06-21
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

Question by:Raland9966
  • 2
LVL 26

Accepted Solution

Nick67 earned 2000 total points
ID: 36582955
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
LVL 74

Expert Comment

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


LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36583051

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



Author Closing Comment

ID: 36584047
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.

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

829 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