object variable or With block variable not set error

[pbuethe]

Hello,

I have the following code to pre-fill some fields on a PDF form.

Function basPreFill(strSample, strFacility, strCaseID As String)
Dim strWSFormName, strSql  As String
Dim AcroApp As Acrobat.CAcroApp
Dim theWSForm As Acrobat.CAcroPDDoc
Dim rstCaseInfo As Recordset
Dim jso As Object

 strWSFormName = conMainFolder & "Sample " & strSample & "\" & strFacility & "\" & strCaseID & "_WSForm.pdf"

Set AcroApp = CreateObject("AcroExch.App")
Set theWSForm = CreateObject("AcroExch.PDDoc")

If theWSForm.Open(strWSFormName) <> False Then   'PC_form already exist
 MsgBox strWSFormName & " already exists. "
 Exit Function
End If

theWSForm.Open ("V:\Master_DB\eChart\Worksheet Template\Review Worksheet_2018-07-01_R3Enable.pdf")
'* get case information

strSql = "SELECT CaseNbr, AdmitDate, DischDate, SequenceNbr, SampleNbr, Provider, County, " & _
[SampleNbr] & ' (' & [ReviewSite] & ' )' As SampleNbrReviewSite, Flag1, Flag2, Flag3,  & _
PatientName, MedRecNbr, MedicaidID, DOB, Age, Sex, LOS, DRG, DRGSOI, DRGDesc  & _
FROM tblWkshtHeader WHERE (((tblWkshtHeader.CaseNbr)= ' & Trim(strCaseID) & "'));"
'MsgBox strSql
Set rstCaseInfo = CurrentDb().OpenRecordset(strSql)
 
If rstCaseInfo.RecordCount <> 1 Then
 'Debug.Print "Check case ID " & strCaseID
 Exit Function
End If

Set jso = theWSForm.GetJSObject
jso.getField("CaseNbr#1").Value = Trim(rstCaseInfo("CaseNbr")) & ""
jso.getField("CaseNbr#1").ReadOnly = True
jso.getField("AdmitDate#1").Value = Trim(rstCaseInfo("AdmitDate")) & ""
jso.getField("AdmitDate#1").ReadOnly = True
jso.getField("DischDate#1").Value = Trim(rstCaseInfo("DischDate")) & ""
jso.getField("DischDate#1").ReadOnly = True
jso.getField("SequenceNbr#0").Value = Trim(rstCaseInfo("SequenceNbr")) & ""
jso.getField("SequenceNbr#0").ReadOnly = True

'there are more jso.getField statements for the rest of the fields, then:

jso.saveAs (strWSFormName)


AcroApp.Exit:
Set AcroApp = Nothing
rstCaseInfo.Close
theWSForm.Close
End Function

When I run this I get the error "object variable or With block variable not set" on the first jso.getField line.

I am running Access 2010.

Thanks in advance for your assistance.

 

[June7]

Can you provide the PDF file for testing? Sample data would be nice as well.

I don't see how the code can successfully compile. Did you run Debug>Compile? SQL construction syntax appears to be wrong - missing quotes. Is this procedure behind a form or report? Use Me. or Me! qualifier for referencing form fields/controls.

strSql = "SELECT CaseNbr, AdmitDate, DischDate, SequenceNbr, SampleNbr, Provider, County, " & _
Me!SampleNbr & "(" & Me!ReviewSite & ") As SampleNbrReviewSite, Flag1, Flag2, Flag3, " & _
"PatientName, MedRecNbr, MedicaidID, DOB, Age, Sex, LOS, DRG, DRGSOI, DRGDesc " & _
"FROM tblWkshtHeader WHERE CaseNbr= '" & Trim(strCaseID) & "';"

 

[The_Doc_Man]

Set a breakpoint on "SET jso=" line just before the line that gives you the actual error. When the code screen comes up and highlights the breakpoint, you can open the Locals window (under the View menu item).

Now single-step (usually F8 key) ONE TIME ONLY. Your highlighted line should be the one that is being called out for the error - but it has not been executed yet so you have not tripped the error object yet.

Find the values in the Locals window for "jso" and "rstCaseInfo" because one of those two items isn't defined. While you are at it, check the value of the strSQL string, which might contribute to the problem if it was the recordset rather than the jso/PDF file.

 

[pbuethe]

The Doc Man,

jso has a value of Nothing.

rstCaseInfo : I expanded it in the Locals window, then expanded "Fields". There was a list of fields "item 1" through "item 21". This is the number of fields, but none show a value. However, when I mouse over "rstCaseInfo" on each jso.getField line, it shows the value of that field.

strSql: I cannot see the whole value in the Locals window. It looked like I wanted when I displayed it with a msgbox.

 

[Gasman]

Are you even creating the Acrobat objects?

 

[pbuethe]

Can you provide the PDF file for testing? Sample data would be nice as well.

I don't see how the code can successfully compile. Did you run Debug>Compile? SQL construction syntax appears to be wrong - missing quotes. Is this procedure behind a form or report? Use Me. or Me! qualifier for referencing form fields/controls.

strSql = "SELECT CaseNbr, AdmitDate, DischDate, SequenceNbr, SampleNbr, Provider, County, " & _
Me!SampleNbr & "(" & Me!ReviewSite & ") As SampleNbrReviewSite, Flag1, Flag2, Flag3, " & _
"PatientName, MedRecNbr, MedicaidID, DOB, Age, Sex, LOS, DRG, DRGSOI, DRGDesc " & _
"FROM tblWkshtHeader WHERE CaseNbr= '" & Trim(strCaseID) & "';"

The code does compile. Somehow the code that I posted is missing the quotes that were there. Here it is again:

strSql = "SELECT CaseNbr, AdmitDate, DischDate, SequenceNbr, SampleNbr, Provider, County, " & _
"[SampleNbr] & ' (' & [ReviewSite] & ' )' As SampleNbrReviewSite, Flag1, Flag2, Flag3, " & _
"PatientName, MedRecNbr, MedicaidID, DOB, Age, Sex, LOS, DRG, DRGSOI, DRGDesc " & _
"FROM tblWkshtHeader WHERE (((tblWkshtHeader.CaseNbr)= '" & Trim(strCaseID) & "'));"

All fields are selected from tblWkshtHeader. SampleNbr and ReviewSite (both fields in tblWkshtHeader) are concatenated to make SampleNbrReviewSite. rstCaseInfo does have the values of the fields.

 

[pbuethe]

Gasman,

When I expand the Acrobat objects in the Locals window it says "No variables". Does this mean they were not created? What would I see if they were created?

 

[Gasman]

Not an expert here, but I would have thought some values would be populated.?
If they are not created, then I believe jso cannot be created. ?

 

[The_Doc_Man]

The object variables are actually pointers to the "real" objects. When the Locals window reveals an object is Nothing, it is telling you that the code that would have filled in that object variable didn't do its job. The error is therefore that you have not instantiated the thing that jso should have referenced.

In that same window you should be able to see ALL of your local variables. I see from the original code samples that you have objects for AcroApp and theWSForm. As they are also objects, they will either be nothing or they will be instantiated in some more complex way. From that same breakpoint, you can see which ones are OK and which ones are not. From the breakpoint I described, you can look at the code scrolling backwards to verify that each object in question has been created and instantiate. When you find one that is "Nothing" when you expected it to be something, you have found the failure.

Since this is an experiment you have to perform, all I can say is that it is possible for your jso to be "hosed up" because one of its precursors is similarly hosed. Only you will be able to know that. But the method I described lets you trace backwards to find the thing that isn't like it should be.

Side note: The syntax below is not giving you what you might have expected, though it will still work.

Dim strWSFormName, strSql  As String

In the above, strWSFormName is a VARIANT, not a string. The correct syntax to make that a string is

Dim strWSFormName [COLOR="Red"]As String[/COLOR], strSql  As String

It really won't hurt you to have that particular error because Variant data types can act as strings if need be - and transparently to your program. HOWEVER, the down side is that you can assign a Variant with another data type during program execution and will not get a Type Mismatch warning if you do so. Variants are chameleons.

 

[JHB]

The code does compile. Somehow the code that I posted is missing the quotes that were there. Here it is again:

strSql = "SELECT CaseNbr, AdmitDate, DischDate, SequenceNbr, SampleNbr, Provider, County, " & _
[COLOR=Red][COLOR=Black]"[/COLOR][/COLOR][B][COLOR=Red][SampleNbr] & ' (' & [ReviewSite] & ' )'[/COLOR][/B] As SampleNbrReviewSite, Flag1, Flag2, Flag3, " & _
"PatientName, MedRecNbr, MedicaidID, DOB, Age, Sex, LOS, DRG, DRGSOI, DRGDesc " & _
"FROM tblWkshtHeader WHERE (((tblWkshtHeader.CaseNbr)= '" & Trim(strCaseID) & "'));"

That can't be correct either.

 

[Cronk]

I've never had need to use PDF objects. However, it seems to me that

Set theWSForm = CreateObject("AcroExch.PDDoc")

is creating an empty object and therefore it would not contain any jso object, let alone have any fields.


Maybe theWSForm should be set to opening an existing PDF document and I don't know the syntax.


JBH, I don't see any problem with

select ..."[SampleNbr] & ' (' & [ReviewSite] & ' )'  as SampleNbrReviewSite

If it was

select [FirstName] & ' (' & [Surname] & ' )'  as Person

an example could be

John(' Smith ')

 

[JHB]

..
JBH, I don't see any problem with

select ..."[SampleNbr] & ' (' & [ReviewSite] & ' )'  as SampleNbrReviewSite

If it was

select [FirstName] & ' (' & [Surname] & ' )'  as Person

an example could be

John(' Smith ')

Yes sorry, you're right.

 

[pbuethe]

Thanks for your replies, I am just getting back to this after being out of the office for a few days.

I modified the code to Dim strWSFormName As String as The_Doc_Man suggested. Then I noticed that the name of the PDF template file was wrong, and corrected it. When I ran the code, it stopped on the same line, but this time with error 424 "object required".

Edit: when I display the locals window, jso now has <No Variables> instead of Nothing.

 

[The_Doc_Man]

Having not worked with PDF from VBA before, I must now defer to others who might know more about it. However, it certainly seems like you have something that is "empty" in that it has no variables. Cronk's comment about creating an empty object seems on point here.

 

[pbuethe]

From some more research into this problem, I modified the code to the following:

Function basPreFill(strSample As String, strFacility As String, strCaseID As String)
Dim strWSFormName As String, strSql  As String
Dim AcroApp As CAcroApp
Dim AVDoc As CAcroAVDoc
Dim theWSForm As CAcroPDDoc
Dim rstCaseInfo As Recordset
Dim jso As Object
Dim OK As Boolean

strWSFormName = conMainFolder & "Inpatient UR Sample " & strSample & "\" & strFacility & "\" & strCaseID & "\" & strCaseID & "_WSForm.pdf"

Set AcroApp = CreateObject("AcroExch.App")
Set AVDoc = CreateObject("AcroExch.AVDoc")


If AVDoc.Open(strWSFormName, "") Then
 MsgBox strWSFormName & " already exists. "
 Exit Function
End If

OK = AVDoc.Open("V:\Master_DB\eChart\Worksheet Template\011_NYSCAID Retrospective Review Worksheet_2018-07-02_R3Enable.pdf", "")
Set AVDoc = AcroApp.GetActiveDoc
Set theWSForm = AVDoc.GetPDDoc()

'* get case information
strSql = "SELECT CaseNbr, AdmitDate, DischDate, SequenceNbr, SampleNbr, Provider, County, " & _
"[SampleNbr] & ' (' & [ReviewSite] & ')' As SampleNbrReviewSite, Flag1, Flag2, Flag3, " & _
"PatientName, MedRecNbr, MedicaidID, DOB, Age, Sex, LOS, DRG, DRGSOI, DRGDesc " & _
"FROM tblWkshtHeader WHERE (((tblWkshtHeader.CaseNbr)= '" & Trim(strCaseID) & "'));"
MsgBox strSql
Set rstCaseInfo = CurrentDb().OpenRecordset(strSql)
 
If rstCaseInfo.RecordCount <> 1 Then
 'Debug.Print "Check case ID " & strCaseID
 Exit Function
End If


Set jso = theWSForm.GetJSObject
jso.getField("CaseNbr#1").Value = Trim(rstCaseInfo("CaseNbr")) & ""
jso.getField("CaseNbr#1").ReadOnly = True
jso.getField("AdmitDate#1").Value = Trim(rstCaseInfo("AdmitDate")) & ""
jso.getField("AdmitDate#1").ReadOnly = True
jso.getField("DischDate#1").Value = Trim(rstCaseInfo("DischDate")) & ""
jso.getField("DischDate#1").ReadOnly = True
jso.getField("SequenceNbr#0").Value = Trim(rstCaseInfo("SequenceNbr")) & ""
jso.getField("SequenceNbr#0").ReadOnly = True
...
'more fields follow

jso.saveAs (strWSFormName)


AcroApp.Exit:

theWSForm.Close
Set theWSForm = Nothing
Set AVDoc = Nothing
Set AcroApp = Nothing
rstCaseInfo.Close
End Function

I still get the error 424, Object required.
However, now Acrobat opens, but gives the message "There was an error opening this document. This file cannot be found."

Also, *sometimes* when I run it, the pdf form is displayed, but the data is not filled in,
and the "there was an error" message still displays. when I click "OK" on the error message then it might display "Adobe Acrobat DC has stopped working"
In that case, when I clicked on "close the program" I got a run-time error "Method 'open' of object 'CAcroAVDoc' failed" and the code is stopped at "If AVDoc.Open(strWSFormName, "") Then";

It seems like I have made some progress, since now Acrobat is opening, but may or may not open the file. What I want to do is, if the file already exists (using the path given by strWSFormName), exit. If not, create it using the template at the path given in the second AVDoc.Open statement, and pre-fill some fields using the data in rstCaseInfo, saving it to the strWSFormName path. Thanks for your comments.

 

[June7]

Can use Dir() function to verify file exists:

If Dir(strWSFormName)<> "" Then

 

[pbuethe]

I solved the problem. It turned out that I was using the wrong names for some of the fields. Basically, I didn't need the numbers on the field names, it was just "CaseNbr" instead of "CaseNbr#1", etc. Thanks for your attention.

 

[Gasman]

Care to post the completed code in case it is useful to someone else please?