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.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]
Have something to add?