Pulling data from PDF to Access DB using GetField: check if drop down is populated-mismatch error

New Here ,
Jan 31, 2021 Jan 31, 2021

Copy link to clipboard


I am using VBA to extract data from a PDF into my access database.  For part of this I need to check if a drop down has been populated before continuing with the rest of the script.  

This line works fine in my database that has an access backend:



If jso.getField("Room10").Value <> " " Then



But when I test in my database that has an SQL backend I get a data mismatch error.  I am unsure if it is the backend that is causing the issue but thought I'd mention it just in case. 

Below is how my script is set up.  It checks each row in a table to see if it is populated.  If the first drop down in that row is not populated then it goes down to the next row until it reaches the last row in the table.

It may just be that my If statment is incorrect but I have tested many options for this and found this one to work on my local machine.



Private Sub JobNumber_Click()

'Copyright Notice: The following code was originally written by theDBguy@gmail.com.
'You are free to use this code in your application, provided this copyright notice remains unchanged. All rights reserved."
'On Error GoTo errHandler

Dim appAcro As Object
Dim docAcro As Object
Dim docPD As Object
Dim jso As Object
Dim fd As Object
Dim strFile As String
Dim lngCounter As Long
Dim HdrOrderNumber As String

Set fd = Application.FileDialog(3) 'filepicker

'select PDF file
With fd
    .AllowMultiSelect = False
    .Filters.Add "PDF Files", "*.pdf"
    .Title = "Locate PDF file"
    If .Show Then
        strFile = .SelectedItems(1)
        Forms!fmnuMainMenu.lblFilePath.Caption = strFile
        Set appAcro = CreateObject("Acroexch.AVDoc")
        If appAcro.Open(strFile, "") Then
            Set docPD = appAcro.getPDDoc()
            Set jso = docPD.getJSObject

'Other bits of extraction here

      If jso.getField("Room10").Value <> " " Then
  'Insert PDF table into tblJobDelivery Room 10
        Dim LHvar10 As String
        LHvar10 = jso.getField("LH10").Value
        If LHvar10 = "Yes" Then
        LHvar10 = -1
        Else: LHvar10 = 0
        End If
        Dim RHvar10 As String
        RHvar10 = jso.getField("RH10").Value
        If RHvar10 = "Yes" Then
        RHvar10 = -1
        Else: RHvar10 = 0
        End If
        Dim Pvar10 As String
        Pvar10 = jso.getField("P10").Value
        If Pvar10 = "Yes" Then
        Pvar10 = -1
        Else: Pvar10 = 0
        End If
   CurrentDb.Execute "INSERT INTO tblJobDetail(Room, OrderNumber,  Width, LH, RH, Pair, Prep, Other ) values ('" _
        + Nz(jso.getField("Room10").Value) + "'," & JobID & ",'" + Nz(CStr(jso.getField("Width10").Value)) + "','" + LHvar10 + "','" + RHvar10 + "','" + Pvar10 + _
        "','" + Nz(jso.getField("PREP10").Value) + "','" + Nz(jso.getField("NOTES10").Value) + "')", dbFailOnError
       End If



 Any suggestions/help is much appreciated


[Question moved to the Acrobat SDK forum by moderator]

Acrobat SDK and JavaScript







Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
no replies

Have something to add?

Join the conversation