Print to PDF with Bullzip. Runtime error 3071.

Fonzo · September 29th, 2010, 02:57 AM

Hello everybody.

I am fairly new to VBA in Access but am programmering a VBA script to print af report in PDF.

There is af call to a DB where i make a while loop that runs through all values.
In each run it is suppose to run a report with a specifik e-mail and user id.

In the while loop i call the print to pdf function with the variables id and mail.
the SECOND time it runs, i tells me that the following line:

Code:

DoCmd.OpenReport "Mail_Tidsregistrering_Historik"

generates the error:
"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)".

I works fine the first time, and generates the file it is suppose to.

The following code calls to database and calls the pdf print function.

Code:

Dim SQL As String
Dim MyDB As DAO.Database, MyRec As DAO.Recordset, MyList As String
Set MyDB = CurrentDb

SQL = "SELECT BrugerID, Email FROM Brugere WHERE Active='Y'"
Set MyRec = MyDB.OpenRecordset(SQL)

While Not MyRec.EOF

Me.UserID = MyRec(0)
MailModtager = MyRec(1)

Call PrintTidsregistreringAsPDF(MyRec(0), MyRec(1))
Sleep (1000)

'Call PrintTidsregistreringFordeltOpgaveAsPDF(MyRec(0), MyRec(1))
'Sleep (2000)

'Call SendMail

Me.UserID = ""
Me.StartDato = ""
Me.SlutDato = ""

MyRec.MoveNext
Wend

MyRec.Close
MyDB.Close

The following code is the PrintTidsregistreringAsPDF function:

Code:

Function PrintTidsregistreringAsPDF(id As String, mail As String)
    Dim pdf_printer_name As String
    Dim pdf_printer_index As Integer
    Dim current_printer_name As String
    Dim current_printer_index As Integer
    Dim i As Integer
    Dim progid As String
    Dim xmldom As Object
    Dim currentdir As String
    Dim pdfwriter As Object
     
    Rem -- Get the directory of the database
    currentdir = GetDatabaseFolder
     
    Rem -- Read the info xml
    Set xmldom = CreateObject("MSXML.DOMDocument")
    xmldom.Load (currentdir & "\info.xml")
     
    Rem -- Get the program id of the automation object.
    'progid = xmldom.SelectSingleNode("/xml/progid").Text

    Rem -- Create the printer automation object
    Set pdfwriter = CreateObject("Bullzip.PDFPrinterSettings")

    Rem -- Printer specific settings
    'pdf_printer_name = pdfwriter.GetPrinterName
    pdf_printer_name = "Bullzip PDF Printer"
     
    Rem -- Find the index of the printer that we want to use
    pdf_printer_index = -1
    current_printer_index = -1
    current_printer_name = Application.Printer.DeviceName
    For i = 0 To Application.Printers.Count - 1
        If Application.Printers.Item(i).DeviceName = pdf_printer_name Then
            pdf_printer_index = i
        End If
        If Application.Printers.Item(i).DeviceName = current_printer_name Then
            current_printer_index = i
        End If
    Next
     
    Rem -- Exit here if the pdf printer was not found
    If pdf_printer_index = -1 Then
        MsgBox "The printer '" & pdf_printer_name & "' was not found on this computer."
        Exit Function
    End If
     
    Rem -- Exit here if the current printer was not found
    If current_printer_index = -1 Then
        MsgBox "The current printer '" & current_printer_name & "' 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
     
    Rem -- Set the printer
    Application.Printer = Application.Printers(pdf_printer_index)
     
    Rem -- Configure the PDF printer
    With pdfwriter
        Rem -- Set the destination file name of the PDF document
         
        FilSti = "F:\Operations\Stig\Kalds liste\Kalds liste\OUT\Mail statistik\"
        FilNavn = id & "-" & Round(Rnd() * 1000000, 0) & " - Mail_Tidsregistrering_Historik.pdf"
        .SetValue "output", FilSti & FilNavn
         
        Rem -- Control the dialogs when printing
        .SetValue "ConfirmOverwrite", "yes"
        .SetValue "ShowSaveAS", "never"
        .SetValue "ShowSettings", "never"
        .SetValue "ShowPDF", "no"
        .SetValue "ShowProgress", "no"
         
        Rem -- Set document properties
        .SetValue "Target", "printer"
        .SetValue "Title", "Mail rapport"
        .SetValue "Subject", "Report generated at " & Now
         
        Rem -- Display page thumbs when the document is opened
        .SetValue "UseThumbs", "yes"
         
        Rem -- Set the zoom factor to 50%
        .SetValue "Zoom", "50"
         
        Rem -- Place a stamp in the lower right corner
        .SetValue "WatermarkText", "SYLLOGIS!"
        .SetValue "WatermarkVerticalPosition", "bottom"
        .SetValue "WatermarkHorizontalPosition", "right"
        .SetValue "WatermarkVerticalAdjustment", "3"
        .SetValue "WatermarkHorizontalAdjustment", "1"
        .SetValue "WatermarkRotation", "45"
        .SetValue "WatermarkColor", "#ff0000"
        .SetValue "WatermarkOutlineWidth", "1"
         
        Rem -- Write the settings to the runonce.ini file
        .WriteSettings True
    End With
     
    Rem -- Run the report
    DoCmd.OpenReport "Mail_Tidsregistrering_Historik"
End Function

Help would be greatly appreciated. How can i avoid the 3071 error? :)

Best regards Fonzo.