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.Clear
.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]
AdChoices