object variable or With block variable not set error (1 Viewer)

pbuethe

Returning User
Local time
Today, 18:27
Joined
Apr 9, 2002
Messages
210
Hello,

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

Code:
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

AWF VIP
Local time
Today, 14:27
Joined
Mar 9, 2014
Messages
5,423
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) & "';"
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:27
Joined
Feb 28, 2001
Messages
27,001
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

Returning User
Local time
Today, 18:27
Joined
Apr 9, 2002
Messages
210
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

Enthusiastic Amateur
Local time
Today, 22:27
Joined
Sep 21, 2011
Messages
14,048
Are you even creating the Acrobat objects?
 

pbuethe

Returning User
Local time
Today, 18:27
Joined
Apr 9, 2002
Messages
210
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:
Code:
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

Returning User
Local time
Today, 18:27
Joined
Apr 9, 2002
Messages
210
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

Enthusiastic Amateur
Local time
Today, 22:27
Joined
Sep 21, 2011
Messages
14,048
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

Immoderate Moderator
Staff member
Local time
Today, 17:27
Joined
Feb 28, 2001
Messages
27,001
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.

Code:
Dim strWSFormName, strSql  As String

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

Code:
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

Have been here a while
Local time
Today, 23:27
Joined
Jun 17, 2012
Messages
7,732
The code does compile. Somehow the code that I posted is missing the quotes that were there. Here it is again:
Code:
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

Registered User.
Local time
Tomorrow, 09:27
Joined
Jul 4, 2013
Messages
2,770
I've never had need to use PDF objects. However, it seems to me that
Code:
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
Code:
select ..."[SampleNbr] & ' (' & [ReviewSite] & ' )'  as SampleNbrReviewSite
If it was
Code:
select [FirstName] & ' (' & [Surname] & ' )'  as Person
an example could be

John(' Smith ')
 

JHB

Have been here a while
Local time
Today, 23:27
Joined
Jun 17, 2012
Messages
7,732
..
JBH, I don't see any problem with
Code:
select ..."[SampleNbr] & ' (' & [ReviewSite] & ' )'  as SampleNbrReviewSite
If it was
Code:
select [FirstName] & ' (' & [Surname] & ' )'  as Person
an example could be

John(' Smith ')
Yes sorry, you're right.
 

pbuethe

Returning User
Local time
Today, 18:27
Joined
Apr 9, 2002
Messages
210
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.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:27
Joined
Feb 28, 2001
Messages
27,001
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

Returning User
Local time
Today, 18:27
Joined
Apr 9, 2002
Messages
210
From some more research into this problem, I modified the code to the following:

Code:
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

AWF VIP
Local time
Today, 14:27
Joined
Mar 9, 2014
Messages
5,423
Can use Dir() function to verify file exists:

If Dir(strWSFormName)<> "" Then
 

pbuethe

Returning User
Local time
Today, 18:27
Joined
Apr 9, 2002
Messages
210
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

Enthusiastic Amateur
Local time
Today, 22:27
Joined
Sep 21, 2011
Messages
14,048
Care to post the completed code in case it is useful to someone else please?
 

Users who are viewing this thread

Top Bottom