Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-12-2019, 08:08 AM   #1
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 456 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Weird Adobe Export Error

Okay, so I'm exporting the results of a query to a fillable PDF and saving them to a different folder. Null sweat, right?

The problem I'm running into is this:
My first two files generate just fine, but the third one kicks back a Type Mismatch error. I've determined which line it is (the PO Number line), and here's the weird part:
  • File one has a null value in PO Number and exports just fine.
  • File two has a value in PO Number, so no issues.
  • File three has a null value in PO Number, and kicks off the error.

From testing, I have determined:
  • If I immediately run the code again, files 1 and 2 are skipped, and file 3 exports perfectly fine.
  • If I don't do an Nz check and just force a value, file 3 exports with no issues.
  • It doesn't actually matter what I use as the second argument in the Nz - it ALWAYS kicks back an error for file 3 at the PO Number line.

I'll include the code below. Not included is the calling code, but all that does is open a recordset and pass it to the function. The recordset is accurate and working as expected. Forgive the over-documentation - this is actually supported by someone else who isn't all that skilled with VBA.

Code:
Private Function CreateBadgeRequests(ByRef rs As DAO.Recordset, _
                                     Optional ByVal IsBatch As Boolean = False) As Long
' ************************************************************
' Created by:       Scott L Prince
' Created on:       2019-03-11
' Parameters:       rs - Recordset containing the request information
'                   IsBatch (OPTIONAL) - Indicates whether or not this is a batch request rather than individual requests.
' Results:          Creates one or more badge requests in the form of PDF files.
' Returns:          0   - Unhandled exception
'                   1   - Completed successfully
'                   2   - Error encountered and trapped, user notified
' Remarks:          Based on code created by Access MVP Leo (theDBGuy).
'                   http://www.accessmvp.com/thedbguy
'                   https://thedbguy.blogspot.com
' ************************************************************

'Adobe PDF Save enumeration.  Combine using OR to use multiple flags.
Const PDSaveIncremental = 0         'Incremental save - saves original plus changes.  Always larger than original.
Const PDSaveFull = 1                'Creates a new save file.
Const PDSaveCopy = 2                'Requires PDSaveFull.  Saves a copy and leaves original untouched.
Const PDSaveCollectGarbage = 32     'Requires PDSaveFull.  Removes unreferenced objects.
Const PDSaveLinearized = 4          'Requires PDSaveFull.  Used to save web documents.

'*** LATE BINDING ***
Dim AcroAppl As Object
Dim AcroDoc As Object
Dim pdfDoc As Object

'*** EARLY BINDING - ADOBE REFERENCE LIBRARY MUST BE ENABLED TO USE THESE ***
'Dim AcroAppl As AcroApp
'Dim AcroDoc As AcroAVDoc
'Dim pdfDoc As AcroPDDoc

'OTHER OBJECT VARIABLES
Dim db As DAO.Database
Dim jso As Object                   'JavaScript object.  Created from Adobe library - no native VBA object, so MUST be late-bound.

'DATA VARIABLES
Dim PDSaveOptions As Long
Dim TemplatePath As String
Dim OutputPath As String

On Error GoTo ErrHandler

    PDSaveOptions = PDSaveFull Or PDSaveCopy        'Use PDSaveFull and PDSaveCopy
    
    'Fillable PDF template
    TemplatePath = AddEndSlash(BADGE_REQUEST_TEMPLATE_PATH) & BADGE_REQUEST_TEMPLATE_NAME
    
    'Confirm template file exists.
    If Dir(TemplatePath) = "" Then
        'File is missing.  Notify user to contact OPM.
        Beep
        MsgBox "The template file '" & TemplatePath & "' cannot be found!" & vbCrLf & vbCrLf & _
               "Please contact OPM for support.", vbCritical, "Business Partner Tracking"
    Else
        If Not rs.EOF Then
            'Create Acrobat object.
            Set AcroAppl = CreateObject("AcroExch.App")
            
            'Create Acrobat document.
            Set AcroDoc = CreateObject("AcroExch.AVDoc")
            
            'Create the PDF.
            If AcroDoc.Open(TemplatePath, "") Then
                With rs
                    Do
                        'Set the export path and file name.
                        OutputPath = AddEndSlash(BADGE_REQUEST_OUTPUT_PATH)
                        
                        If IsBatch Then
                            OutputPath = OutputPath & BADGE_REQUEST_BATCH_OUTPUT_NAME
                        Else
                            OutputPath = OutputPath & BADGE_REQUEST_OUTPUT_NAME & " For " & .Fields("FirstName") & " " & .Fields("LastName") & ".pdf"
                        End If

                        If Dir(OutputPath) <> "" Then
                            'Notify the user that the file already exists, then move to the next item.
                            MsgBox "The file " & OutputPath & " already exists!" & vbCrLf & vbCrLf & _
                                   "This file will NOT be created.", vbInformation, "Business Partner Tracking"
                        Else
                            'Access the PDF.
                            Set pdfDoc = AcroDoc.GetPDDoc()
                            
                            'Update form fields.
                            'Note that ALL fields must be checked for NULLS.  If Adobe receives a null value, it generates a fatal error.
                            Set jso = pdfDoc.GetJSObject
                            jso.GetField("Badge Number").Value = Nz(.Fields("BadgeNumber").Value, "")
                            jso.GetField("First Name").Value = Nz(.Fields("FirstName").Value, "")
                            jso.GetField("Last Name").Value = Nz(.Fields("LastName").Value, "")
                            jso.GetField("SSN last 5").Value = Nz(.Fields("LastFiveSSN").Value, "")
                            jso.GetField("Vendor Name").Value = Nz(.Fields("Business Partner Name").Value)
                            jso.GetField("Leader Badge Number").Value = Nz(.Fields("BCBSM Leader Badge ID").Value, "")
                            jso.GetField("Leader Name").Value = Nz(.Fields("BCBSM Leader Full Name").Value, "")
                            jso.GetField("Leader Phone Number").Value = Nz(.Fields("BCBSM Leader Phone Number").Value, "")
                            jso.GetField("Cost Center").Value = Nz(.Fields("BCBSM Leader Cost Center").Value, "")
                            jso.GetField("Start Date").Value = CStr(.Fields("Start Date").Value)
                            jso.GetField("End Date").Value = CStr(.Fields("End Date").Value)
                            jso.GetField("PO Number").Value = Nz(.Fields("PO Number").Value, "")
                            jso.GetField("Former Employee").Value = Nz(.Fields("Former Employee").Value, "")
                            jso.GetField("Primary Location").Value = Nz(.Fields("BCBSMPrimaryLocation").Value, "")
                            jso.GetField("Department Name").Value = Nz(.Fields("BCBSM Leader Department Name").Value, "")
                            jso.GetField("Mail Code").Value = Nz(.Fields("BCBSM Leader Mail Code").Value, "")
                            jso.GetField("Comments").Value = Nz(.Fields("Comments").Value, "")
                        
                            'Save a COPY of the modified file.
                            pdfDoc.Save PDSaveOptions, OutputPath
                        
                            'Clear the document reference for the next file.
                            Set pdfDoc = Nothing
                        End If          'Output file exists check
                        
                        'Next record
                        .MoveNext
                    Loop Until .EOF
                End With
                'Return success code.
                CreateBadgeRequests = 1
            End If                      'AcroDoc.Open check
        End If                          'rs.EOF check
    End If                              'Template exists check
    
ProcExit:
    On Error Resume Next
    
    If Not jso Is Nothing Then Set jso = Nothing
    If Not pdfDoc Is Nothing Then Set pdfDoc = Nothing
    If Not AcroDoc Is Nothing Then
        AcroDoc.Close (True)
        Set AcroDoc = Nothing
    End If
    If Not AcroAppl Is Nothing Then Set AcroAppl = Nothing
    If Not db Is Nothing Then Set db = Nothing

    Exit Function
    
ErrHandler:
    CreateBadgeRequests = 2
    Beep
    MsgBox "An error has been encountered!  Please notify OPM with the following information:" & vbCrLf & vbCrLf & _
           "Tool Name:" & vbTab & "Business Partner Tracking" & vbCrLf & _
           "Procedure:" & vbTab & Me.Name & ".CreateBadgeRequests" & vbCrLf & _
           "Error Number:" & vbTab & Err.Number & vbCrLf & _
           "Description:" & vbTab & Err.Description, vbCritical, "Business Partner Tracking"
    Resume ProcExit

End Function
So my question is this: Does anyone have a freaking clue what is causing this intermittent error and how to fix it?

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 03-12-2019, 08:31 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,407
Thanks: 162
Thanked 1,730 Times in 1,700 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Weird Adobe Export Error

Hmmm. I think I've read your tests correctly.

What if you juggle the null PO position or have nulls in all three...
Or don't have nulls anywhere....
Same issue?
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
Frothingslosh (03-12-2019)
Old 03-12-2019, 09:25 AM   #3
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 456 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Weird Adobe Export Error

Quote:
Originally Posted by Minty View Post
Hmmm. I think I've read your tests correctly.

What if you juggle the null PO position or have nulls in all three...
Or don't have nulls anywhere....
Same issue?
  • Nulls in PO Number for all three: No error encountered
  • Values in PO Number for all three: No error encountered
  • Change the order of field assignments: No change. Export 3 always fails on PO no matter where in the list it appears. (Except, of course, when 1 and 2 already exist and were skipped.)

It has something to do with the PO Number field. It's as if it just suddenly skips the Nz function and just passes the NULL through anyway.

As further testing, I set the value of a field in each record to null. Record 1 exported just fine, with Nz returning an empty string, which the PDF accepted. When record 2 had nulls, however, IT started returning the data type mismatch error. It's almost like Nz is working for the first loop, but then not bothering to substitute in passes 2 and on.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 03-12-2019, 09:34 AM   #4
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 456 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Weird Adobe Export Error

Okay, figured it out. I had to move the AcroDoc opening and assignment inside the do loop, so that a fresh one was created each time.

This was happening because some of the fields are flagged as 'required', and while Adobe would let me assign an empty string to an empty required field, it would NOT allow me to change an existing value to an empty string.

Working code is below, but no color coding this time, I'm afraid:
Code:
Private Function CreateBadgeRequests(ByRef rs As DAO.Recordset, _
                                     Optional ByVal IsBatch As Boolean = False) As Long
' ************************************************************
' Created by:       Scott L Prince
' Created on:       2019-03-11
' Parameters:       rs - Recordset containing the request information
'                   IsBatch (OPTIONAL) - Indicates whether or not this is a batch request rather than individual requests.
' Results:          Creates one or more badge requests in the form of PDF files.
' Returns:          0   - Unhandled exception
'                   1   - Completed successfully
'                   2   - Error encountered and trapped, user notified
' Remarks:          Based on code created by Access MVP Leo (theDBGuy).
'                   http://www.accessmvp.com/thedbguy
'                   https://thedbguy.blogspot.com
' ************************************************************

'Adobe PDF Save enumeration.  Combine using OR to use multiple flags.
Const PDSaveIncremental = 0         'Incremental save - saves original plus changes.  Always larger than original.
Const PDSaveFull = 1                'Creates a new save file.
Const PDSaveCopy = 2                'Requires PDSaveFull.  Saves a copy and leaves original untouched.
Const PDSaveCollectGarbage = 32     'Requires PDSaveFull.  Removes unreferenced objects.
Const PDSaveLinearized = 4          'Requires PDSaveFull.  Used to save web documents.

'*** LATE BINDING ***
Dim AcroAppl As Object
Dim AcroDoc As Object
Dim pdfDoc As Object

'*** EARLY BINDING - ADOBE REFERENCE LIBRARY MUST BE ENABLED TO USE THESE ***
'Dim AcroAppl As AcroApp
'Dim AcroDoc As AcroAVDoc
'Dim pdfDoc As AcroPDDoc

'OTHER OBJECT VARIABLES
Dim db As DAO.Database
Dim jso As Object                   'JavaScript object.  Created from Adobe library - no native VBA object, so MUST be late-bound.

'DATA VARIABLES
Dim PDSaveOptions As Long
Dim TemplatePath As String
Dim OutputPath As String

On Error GoTo ErrHandler

    PDSaveOptions = PDSaveFull Or PDSaveCopy        'Use PDSaveFull and PDSaveCopy
    
    'Fillable PDF template
    TemplatePath = AddEndSlash(BADGE_REQUEST_TEMPLATE_PATH) & BADGE_REQUEST_TEMPLATE_NAME
    
    'Confirm template file exists.
    If Dir(TemplatePath) = "" Then
        'File is missing.  Notify user to contact OPM.
        Beep
        MsgBox "The template file '" & TemplatePath & "' cannot be found!" & vbCrLf & vbCrLf & _
               "Please contact OPM for support.", vbCritical, "Business Partner Tracking"
    Else
        If Not rs.EOF Then
        
        'Create Acrobat object.
        Set AcroAppl = CreateObject("AcroExch.App")
        
            With rs
                Do
                    'Create Acrobat document.
                    Set AcroDoc = CreateObject("AcroExch.AVDoc")
                    
                    'Create the PDF.
                    If AcroDoc.Open(TemplatePath, "") Then
                    
                        'Set the export path and file name.
                        OutputPath = AddEndSlash(BADGE_REQUEST_OUTPUT_PATH)
                        
                        If IsBatch Then
                            OutputPath = OutputPath & BADGE_REQUEST_BATCH_OUTPUT_NAME
                        Else
                            OutputPath = OutputPath & BADGE_REQUEST_OUTPUT_NAME & " For " & .Fields("FirstName") & " " & .Fields("LastName") & ".pdf"
                        End If
    
                        If Dir(OutputPath) <> "" Then
                            'Notify the user that the file already exists, then move to the next item.
                            MsgBox "The file " & OutputPath & " already exists!" & vbCrLf & vbCrLf & _
                                   "This file will NOT be created.", vbInformation, "Business Partner Tracking"
                        Else
                            'Access the PDF.
                            Set pdfDoc = AcroDoc.GetPDDoc()
                            
                            'Update form fields.
                            'Note that ALL fields must be checked for NULLS.  If Adobe receives a null value, it generates a fatal error.
                            Set jso = pdfDoc.GetJSObject
                            jso.GetField("Badge Number").Value = Nz(.Fields("BadgeNumber").Value, "")
                            jso.GetField("First Name").Value = Nz(.Fields("FirstName").Value, "")
                            jso.GetField("Last Name").Value = Nz(.Fields("LastName").Value, "")
                            jso.GetField("SSN last 5").Value = Nz(.Fields("LastFiveSSN").Value, "")
                            jso.GetField("Vendor Name").Value = Nz(.Fields("Business Partner Name").Value)
                            jso.GetField("Leader Badge Number").Value = Nz(.Fields("BCBSM Leader Badge ID").Value, "")
                            jso.GetField("Leader Name").Value = Nz(.Fields("BCBSM Leader Full Name").Value, "")
                            jso.GetField("Leader Phone Number").Value = Nz(.Fields("BCBSM Leader Phone Number").Value, "")
                            jso.GetField("Cost Center").Value = Nz(.Fields("BCBSM Leader Cost Center").Value, "")
                            jso.GetField("Start Date").Value = CStr(.Fields("Start Date").Value)
                            jso.GetField("End Date").Value = CStr(.Fields("End Date").Value)
                            jso.GetField("PO Number").Value = Nz(.Fields("PO Number").Value, "")
                            jso.GetField("Former Employee").Value = Nz(.Fields("Former Employee").Value, "")
                            jso.GetField("Primary Location").Value = Nz(.Fields("BCBSMPrimaryLocation").Value, "")
                            jso.GetField("Department Name").Value = Nz(.Fields("BCBSM Leader Department Name").Value, "")
                            jso.GetField("Mail Code").Value = Nz(.Fields("BCBSM Leader Mail Code").Value, "")
                            jso.GetField("Comments").Value = Nz(.Fields("Comments").Value, "")
                        
                            'Save a COPY of the modified file.
                            pdfDoc.Save PDSaveOptions, OutputPath
                        
                            'Clear the document reference for the next file.
                            Set pdfDoc = Nothing
                            Set jso = Nothing
                            AcroDoc.Close (True)
                            Set AcroDoc = Nothing
                        End If          'Output file exists check
                    End If              'AcroDoc.Open check
                    
                    'Next record
                    .MoveNext
                Loop Until .EOF
            End With
            'Return success code.
            CreateBadgeRequests = 1
        End If                          'rs.EOF check
    End If                              'Template exists check
    
ProcExit:
    On Error Resume Next
    
    If Not jso Is Nothing Then Set jso = Nothing
    If Not pdfDoc Is Nothing Then Set pdfDoc = Nothing
    If Not AcroDoc Is Nothing Then
        AcroDoc.Close (True)
        Set AcroDoc = Nothing
    End If
    If Not AcroAppl Is Nothing Then Set AcroAppl = Nothing
    If Not db Is Nothing Then Set db = Nothing

    Exit Function
    
ErrHandler:
    CreateBadgeRequests = 2
    Beep
    MsgBox "An error has been encountered!  Please notify OPM with the following information:" & vbCrLf & vbCrLf & _
           "Tool Name:" & vbTab & "Business Partner Tracking" & vbCrLf & _
           "Procedure:" & vbTab & Me.Name & ".CreateBadgeRequests" & vbCrLf & _
           "Error Number:" & vbTab & Err.Number & vbCrLf & _
           "Description:" & vbTab & Err.Description, vbCritical, "Business Partner Tracking"
    Resume ProcExit

End Function
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 03-12-2019, 07:37 PM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,354
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Weird Adobe Export Error

Quote:
I had to move the AcroDoc opening and assignment inside the do loop, so that a fresh one was created each time.
Glad you solved that. Your solution suggests that the automation interface was only initializing the object on creation, not on opening. Probably a holdover from those utilities that remember where you last did something and continue to operation there rather than return to a default folder each time.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Reply

Tags
adobe , type mismatch , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
HELP! Access 2007: Weird behavior in an export word function Bevos Modules & VBA 2 04-21-2011 07:39 PM
Weird Error Djblois Forms 23 07-20-2009 07:20 AM
Adobe - Bound Object - OLE Server Error TJBernard General 5 11-15-2006 10:16 AM
OLE Server error with Adobe Acrobat TJBernard General 0 10-11-2005 02:23 PM
Weird Error OxDavis Forms 1 09-02-2005 06:47 AM




All times are GMT -8. The time now is 09:55 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World