I keep getting Error 5941 trying to fill a Word 2013 form

[mwvmathewson]

Hi! I wonder if anyone can help work out what is going wrong with this code. I am trying to fill a Word document (Insurance Form) by putting text fields in their table document and filling it from a stored procedure. Every request is a single one so I request the relevant numbers using input boxes. The stored procedure returns the data required but Word element sees no form fields. I have been trying to sort this for days! :banged:

Everything runs fine but nothing is returned to the document. Data is fine when debug.print used on it. Problem I think is the document

Code:

Private Sub VCFOALperCertificate_Click()

Dim intCertNum As Integer
Dim intDeclaration As Integer

'Get user input for foal
intCertNum = InputBox("Enter the Certificate Number")
intDeclaration = InputBox("Enter the Declaration Number")

On Error GoTo Err_VCFOALperCertificate_Click

'Print Veterinary Certificate for chosen foal

Dim appWord As Word.Application
Dim doc As Word.Document

'Avoid error 429 when Word isn't open.

On Error Resume Next

Err.Clear

'Set appWord object variable to running instance of Word

Set appWord = GetObject(, "Word.Application")

If Err.Number <> 0 Then

'If Word isn't open, create a new instance of it.

Set appWord = New Word.Application

End If

Set doc = appWord.Documents.Open("Document Path", , False)

' Initialize variables.
Dim cn As New ADODB.Connection
Dim ServerName As String, DatabaseName As String

' Put text box values into connection variables.
ServerName = "MyServer"
DatabaseName = "dbName"

' Specify the OLE DB provider.
cn.Provider = "sqloledb"

' Set SQLOLEDB connection properties.
cn.Properties("Data Source").Value = ServerName
cn.Properties("Initial Catalog").Value = DatabaseName

' Windows NT authentication.
cn.Properties("Integrated Security").Value = "SSPI"

' Open the database.
cn.Open


Dim str As String
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

str = "EXEC spVetCert " & intCertNum & " , " & intDeclaration & ""

Set doc = ActiveDocument
If doc.ProtectionType <> wdNoProtection Then doc.Unprotect

rst.Open str, cn

With doc
.FormFields("Insured").Result = rst("Insured")
.FormFields("Certificate").Result = rst("Identifier")
.FormFields("Sire").Result = rst("Sire")
.FormFields("Dam").Result = rst("Dam")
.FormFields("Breed").Result = rst("Breed")
.FormFields("Sex").Result = rst("Sex")
.Visible = True
.Activate
End With

Set doc = Nothing

Set appWord = Nothing

Exit Sub


Err_VCFOALperCertificate_Click:
MsgBox Err.Number & ": " & Err.Description

End Sub

 

[JHB]

...
Everything runs fine but nothing is returned to the document. Data is fine when debug.print used on it. Problem I think is the document

Actually you can't know if all is running fine because you've a "On Error Resume Next", (one of the worst error handling you can have), in your code.
Comment the error handling out, then try to run the code.

 

[arnelgp]

can you comment this line, and test:

'Set doc = ActiveDocument

 

[mwvmathewson]

Thank you to both JHB and arnelgp. Taking both your points in order of posting.

1. Comment out the 'On Error Resume Next' delivers an error dialog box '5941: The requested member of the collection does not exist'.
   
2. The 'Set doc = ActiveDocument' was an afterthought on a previous attempt to get it to do what I wanted. Commenting it out makes no difference - nor does removing it permanently.

The issue is, I think, the form fields on the Word document. Previous versions of Word were easier to work with. When I use the immediate window to try and establish what the problem is '? doc' returns the correct document but '?doc.FormFields.Count' returns 0. From this I assume document is open but the form fields are not being recognised as such despite setting the document up as the Technet article suggests for 2013.

 

[arnelgp]

how about if your put the .activate right after opening the doc:

Set doc = appWord.Documents.Open("Document Path", , False)
doc.Activate

 

[mwvmathewson]

Problem solved - to get Access to pass to Word .FormFields("FieldName").Result I needed to use legacy form text boxes and not the ones which were obvious.

Why does Microsoft not produce better documentation - I have wasted hours on this. I can't find any reference to anyone else having this issue.