manipulating checkboxes on pdf in ms access vba

[ricardus maximus]

I'm looping through a data set and populating a pdf with the relevant data and saving the pdfs according to a naming convention.
The issue that I am having is with checking designated "check Boxes" on the pdf . When the pdf is generated it displays the checked box. When it is save... the checked box doesn't persist. I believe this has something to do with the Default value associated with the checkbox. I do not know how to set the default value for the check box programmatically. I would think this is pretty straight forward solution.

Dim FileNm As String
Dim FileNm2 As String

Dim aApp    As Acrobat.AcroApp
Dim aAVDoc  As Acrobat.AcroAVDoc
Dim aPDDoc  As Acrobat.AcroPDDoc
Dim JSO     As Object 'There is no explicit class type for the JSObject

FileNm = "\\its\fss\Plans\PEO-MEP\RichAnthony\EditPDF\IRS Form 1041_2023.pdf"
FileNm2 = "\\its\fss\Plans\PEO-MEP\RichAnthony\EditPDF\1041_Reports\"

Set aApp = CreateObject("AcroExch.App")

Set aAVDoc = CreateObject("AcroExch.AVDoc")

If aAVDoc.Open(FileNm, "") Then

    Set aPDDoc = aAVDoc.GetPDDoc()
    Set JSO = aPDDoc.GetJSObject()
    
    'Debugging - output all fields
   ' For i = 0 To JSO.numFields - 1
      '  Set Field = JSO.getField(JSO.getNthFieldName(i))
      '  Debug.Print i, Field.Name, Field.Value, Field.Type, Field.Page
   ' Next
    
   ' MsgBox "This is a pause."
    
    
    'Create Result set
    Dim rs As Object, strSql As String, GroupAccount As String
    Dim FilePath As String, FileName As String, Criteria As String
    Dim xFilePrefix As String
    
    Dim xBegDate As String
    Dim xEndDate As String
    Dim xYrEnd As String
    Dim xPlanName As String
    Dim xTrustCompany As String
    Dim xTrustCompanyaddress1 As String
    Dim xTrustCompanyaddress2 As String
    Dim xOTC_EFF_DATE As String
    Dim xEIN As String
    Dim xFiduciaryEIN As String
    
    
    xBegDate = "JANUARY 01"
    xEndDate = "DECEMBER 31"
    xYtEnd = "23"
    xTrustCompany = "EMPOWER TRUST COMPANY LLC"
    xTrustCompanyaddress1 = "8525 E. ORCHARD RD 8T3"
    xTrustCompanyaddress2 = "GREENWOOD VILLAGE, CO 80111-5002"
    xFiduciaryEIN = "84-1455663"
    xFilePrefix = "IRS 1041 "
    
    'FilePath = SelectFolder & "\"                    This will be used to navigate to where the 1041 output will be dropped.
          
    Set rs = CreateObject("ADODB.Recordset")
    strSql = "Select * FROM [Tbl Plan Data];"
    rs.Open strSql, CurrentProject.Connection
      
    Do While Not rs.EOF
    
           GroupAccount = rs![Group Account]
           xPlanName = rs![Plan Name]
           xOTC_EFF_DATE = rs![OTC_Effective_Date]   'Need to add to table.
          
           If IsNull(rs![Trust EIN]) Then
          
                xEIN = ""
                
            Else
            
                xEIN = rs![Trust EIN]   'Need to add to table.
                
            End If
          
        
          
           rs.MoveNext
          
           FileName = FileNm2 & xFilePrefix & " " & GroupAccount & ".pdf"
           Criteria = "[Group Account] = '" & GroupAccount & "'"
          
            
          
            
            JSO.getField("topmostSubform[0].Page1[0].DateNameAddress_ReadOrder[0].f1_1[0]").Value = xBegDate
            JSO.getField("topmostSubform[0].Page1[0].DateNameAddress_ReadOrder[0].f1_2[0]").Value = xEndDate
            JSO.getField("topmostSubform[0].Page1[0].DateNameAddress_ReadOrder[0].f1_3[0]").Value = xYtEnd
            JSO.getField("topmostSubform[0].Page1[0].DateNameAddress_ReadOrder[0].f1_4[0]").Value = xPlanName
            JSO.getField("topmostSubform[0].Page1[0].DateNameAddress_ReadOrder[0].f1_5[0]").Value = xTrustCompany
            JSO.getField("topmostSubform[0].Page1[0].DateNameAddress_ReadOrder[0].f1_6[0]").Value = xTrustCompanyaddress1
            JSO.getField("topmostSubform[0].Page1[0].DateNameAddress_ReadOrder[0].f1_7[0]").Value = xTrustCompanyaddress2
            JSO.getField("topmostSubform[0].Page1[0].LinesC-E[0].f1_10[0]").Value = xOTC_EFF_DATE
            JSO.getField("topmostSubform[0].Page1[0].LinesC-E[0].f1_9[0]").Value = xEIN
            JSO.getField("topmostSubform[0].Page1[0].SignatureDate_ReadOrder[0].SignatureDate_ReadOrder[0].f1_47[0]").Value = xFiduciaryEIN

            JSO.getField("topmostSubform[0].Page1[0].LinesA-B[0].c1_6[0]").Value = 1    'This is the portion of the code that doesn't persist.
            
            
        
            aPDDoc.Save PDSaveIncremental, FileName 'Save changes to the PDF document
        

    
    Loop
    
    aPDDoc.Close

End If

'Close the PDF; the True parameter prevents the Save As dialog from showing

aAVDoc.Close (True)
rs.Close
'Some cleaning

Set aAVDoc = Nothing
Set aPDDoc = Nothing
Set JSO = Nothing
Set rs = Nothing


End Sub

 

[theDBguy]

Hi. Welcome to AWF!

Can you post a copy of the PDF form your trying to fill out?

 

[ricardus maximus]

Hi. Welcome to AWF!

Can you post a copy of the PDF form your trying to fill out?

 

[ricardus maximus]

The check boxes are whats causing issues. Grantor Trust for example.

 

[theDBguy]

The check boxes are whats causing issues. Grantor Trust for example.

Thanks for posting a copy of the PDF. I just wanted to double-check that you were assigning the correct value to the checkbox , which you were.

I have run into this same issue before with another poster. In both of your cases, you are both trying to populate a government generated/owned PDF form. My best guess, (this was the same thing I told him) is that the owner/designer of the form added some security settings that's causing this behavior. The other poster was able to solve his problem by converting the PDF form to a Word Doc and then converting the Word Doc into a new fillable PDF form, which basically stripped out all the security features. You might not be willing to do that, but you could give it a try as well.

Good luck!

 

[arnelgp]

use the attached fillable pdf and change your code to:

JSO.getField("topmostSubform[0].Page1[0].LinesA-B[0].c1_6[0]").value = "1"    'This is the portion of the code that doesn't persist.

 

[ricardus maximus]

use the attached fillable pdf and change your code to:

JSO.getField("topmostSubform[0].Page1[0].LinesA-B[0].c1_6[0]").value = "1"    'This is the portion of the code that doesn't persist.

 

[ricardus maximus]

Using the attached pdf you provided and modifying the code actually doesn't produce any pdfs. I bet the JSO object is creating. When I modify code line for the checkbox and use my original pdf it abends because the "1" is the wrong data type.

 

[ricardus maximus]

Thanks for posting a copy of the PDF. I just wanted to double-check that you were assigning the correct value to the checkbox , which you were.

I have run into this same issue before with another poster. In both of your cases, you are both trying to populate a government generated/owned PDF form. My best guess, (this was the same thing I told him) is that the owner/designer of the form added some security settings that's causing this behavior. The other poster was able to solve his problem by converting the PDF form to a Word Doc and then converting the Word Doc into a new fillable PDF form, which basically stripped out all the security features. You might not be willing to do that, but you could give it a try as well.

Good luck!

I originally was going to populate these fields using python pypdf library. The issue that seems to be prevalent with python seems similar to this: it is updating an preview component of the form.

 

[theDBguy]

I originally was going to populate these fields using python pypdf library. The issue that seems to be prevalent with python seems similar to this: it is updating an preview component of the form.

Unfortunately, what I described earlier is the only workaround I know so far. My bet is it's got something to do with the pdf file's security settings.

 

[arnelgp]

i don't know about your Acrobat, but when i Un-Restrict your pdf (the one i uploaded earlier) and changed the code,
it did create the pdf (see the video on the zip file).

you changed the path of the pdf on the sample demo.
note that i comment out the recordset (since i don't have it).
after you change the path, run the code.

just maybe it is on your recordset that has problem that is
why the pdf is not created, i don't know. maybe you can upload it.

or maybe it has to do with your Acrobat?

demo.zip