[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register 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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

649 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