Solved

Access 2007 problem printing batch of reports to Bullzip PDF Printer

Posted on 2011-09-22
4
894 Views
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
-Ralph

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

    DoEvents
 
    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
    Next
 
    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
   rs.MoveNext

      Wend
   End With

End Function

Open in new window

Output.png
EMERALD-FARMS.PDF
0
Comment
Question by:Raland9966
  • 2
4 Comments
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
Comment Utility
MS Access 2007+ has native pdf support.
For 2007, you have to download and install it
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=9943
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
I (and many Experts here) use this and I have never had an issue with creating hundreds of PDF's at a time.
http://www.lebans.com/reporttopdf.htm


<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)...

JeffCoachman

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Nick,

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

;-)

Jeff
0
 

Author Closing Comment

by:Raland9966
Comment Utility
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.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 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

10 Experts available now in Live!

Get 1:1 Help Now