Fill Access Form from Word Form

[kentover]

I have an Access DB front end that is connected to an SQL Server 2000 backend. We use a MS Word form to collect info that will be uploaded into the Access form after clicking the Access form upload button that I have created.

I am able to open the doc and activate it but no reference to any field is recognized. I have added the MS Word Object reference and can send data to the Word form but cannot pull data from the Word form.

I have included some of the code for one field and most of the methods that I have tried. All of the code is in the Access form(s).

Question: How do I get the values of a MS Word form field to insert into an Access form? There is no working code below so all attempts are commented out.



Dim strPath As String
Dim oWord As Object
Dim oDoc As Object

strPath = Me.DOC_LOC
'Open MS-Word via OLE.
Set oWord = CreateObject("word.application")
'Open the form document.
Set oDoc = oWord.Documents.Open(strPath)
oWord.Documents(strPath).Activate
'oWord.Document(strPath).Activate
'oDoc.Document(strPath).Activate

'Form_frmREQSDATA.REQFRMBY.Value = oWord.Document.Bookmarks.FormFields("REQFORMBY").Result
'Form_frmREQSDATA.REQFRMBY.Value = CStr(oDoc.Fields("REQFORMBY").Result)
' Form_frmREQSDATA.REQFRMBY.Value = ActiveDocument.Bookmarks.Item("REQFRMBY").Range.Text
' Form_frmREQSDATA.REQFRMBY.Value = ActiveDocument.Bookmarks.Item("REQFRMBY").Range.Text

'' ActiveDocument.Bookmarks.Item("REQFRMBY").Select
'' Form_frmREQSDATA.REQFRMBY.Value = oWord.Selection.Text

oDoc.Close
Set oDoc = Nothing
Set oWord = Nothing
DoCmd.Close acForm, "frmUpload_Requirements", acSaveNo




Thank you in advance for any help or direction.
KO

 

[Remou]

I think you want:

[tt]oDoc.FormFields("NameOfField").Result

Or

oDoc.FormFields(1).Result[/tt]

 

[kentover]

Remou,

Thank you for the quick reply. I tried the code
oDoc.FormFields("NameOfField").Result
but got the following error.


Runtime Error 5941
The requested member of the collection does not exist.


In all of my attempts I have gotten that error or a type mismatch error. I am not sure how to determine the field number as used in your second suggestion. I have several fields on the Word form and the ones that I am hitting are further in the document.

Do you think that I have activated the doc appropriately to reference the field values?

 

[Remou]

Yes, I do. I think you have not got the right name. You can open the Word document and try some code like so:

For i = 1 To ActiveDocument.FormFields.Count
    Debug.Print ActiveDocument.FormFields(i).Name
    Debug.Print ActiveDocument.FormFields(i).Result
Next

 

[kentover]

Within the Word form and hitting the form from Access still gives me the error that the member does not exist.

 

[Remou]

The snippet above when run in the word document should list the form field names in the immediate window. Did you try it?

 

[kentover]

Yes, the count was 0 each time.

 

[Remou]

In that case, it appears that you do not have any form fields. Perhaps you have bookmarks?

For i = 1 To ActiveDocument.Bookmarks.Count
    Debug.Print ActiveDocument.Bookmarks(i).Range
    Debug.Print ActiveDocument.Bookmarks(i).Name
Next

 

[kentover]

Yes, I am using bookmarks but I did create fields as well. For some reason they are not registering... I have tried hundreds of combinations but still cannot get the values pulled over. At best I was able to pull some type of object over but it appeared as a small box like a line feed or something. Also, I have searched everywhere for info on how to accomplish this but there does not seem to be much out there on pulling data from Word. Many end users do not have Access and Word is part of the standard PC image, so it has to be Word for the end user form.

Oh, by the way, the RANGE values did not return. The value returned was the "box" like the one mentioned above. I was able to return the BOOKMARKIDs though.

I have some recent examples of what does not work commented out in the code below.


strPath = Me.DOC_LOC
Dim oWord As Object
Dim oDoc As Object

Set oWord = CreateObject("word.application")
Set oDoc = oWord.Documents.Open(strPath)
oWord.Documents(strPath).Activate

' Form_frmREQSDATA.REQFRMBY.Value = oDoc.Document.Bookmarks.Item(1).Value
' Form_frmREQSDATA.REQFRMBY.Value = oDoc.Documents.Bookmarks(1).Value
' Form_frmREQSDATA.REQFRMBY.Value = oDoc.Document.Bookmarks(1).Value
' Form_frmREQSDATA.REQFRMBY.Value = oDoc.Documents.Bookmark(1).Value
' Form_frmREQSDATA.REQFRMBY.Value = oDoc.Document.Bookmark(1).Value
' Form_frmREQSDATA.REQFRMBY.Value = oWord.Documents.Bookmarks(1).Value
' Form_frmREQSDATA.REQFRMBY.Value = oWord.Document.Bookmarks(1).Value
' Form_frmREQSDATA.REQFRMBY.Value = oWord.Document.Bookmark(1).Text
' Form_frmREQSDATA.REQFRMBY.Value = oWord.Document.Bookmark(1).Result
' Form_frmREQSDATA.REQFRMBY.Value = oWord.Document.Bookmark(1).Value
' Form_frmREQSDATA.REQFRMBY.Value = oDoc.Bookmarks.Item(1).Result
' Form_frmREQSDATA.REQFRMBY.Value = oDoc.Bookmarks.Item(1).Value
' Form_frmREQSDATA.REQFRMBY.Value = oWord.Bookmarks.Item(1).Result
' Form_frmREQSDATA.REQFRMBY.Value = oWord.Bookmarks.Item(1).Value
' Form_frmREQSDATA.REQFRMBY.Value = oDoc.Bookmarks(1).Value
' Form_frmREQSDATA.REQFRMBY.Value = oDoc.Bookmarks(1).Result
' Form_frmREQSDATA.REQFRMBY.Value = oDoc.Bookmark(1).Value
' Form_frmREQSDATA.REQFRMBY.Value = oDoc.Bookmark(1).Text
' Form_frmREQSDATA.REQFRMBY.Value = oWord.Bookmark(1).Result
' Form_frmREQSDATA.REQFRMBY.Value = oWord.Bookmark(1).Value
' Form_frmREQSDATA.REQFRMBY.Value = oWord.Bookmark(1).Text
' Form_frmREQSDATA.REQFRMBY.Value = oDoc.ActiveDocument.Bookmark(1).Text
' Form_frmREQSDATA.REQFRMBY.Value = oDoc.ActiveDocument.Bookmark(1).Value
' Form_frmREQSDATA.REQFRMBY.Value = oDoc.ActiveDocument.Bookmark(1).Result
' Form_frmREQSDATA.REQFRMBY.Value = oDoc.ActiveDocument.Bookmarks.Item(1).Result
' Form_frmREQSDATA.REQFRMBY.Value = oWord.ActiveDocument.Bookmark(1).Text
' Form_frmREQSDATA.REQFRMBY.Value = oWord.ActiveDocument.Bookmark(1).Value
' Form_frmREQSDATA.REQFRMBY.Value = oWord.ActiveDocument.Bookmark(1).Result
' Form_frmREQSDATA.REQFRMBY.Value = oDoc.ActiveDocument.Bookmarks(1).Result
' Form_frmREQSDATA.REQFRMBY.Value = oWord.ActiveDocument.Bookmarks(1).Result
' Form_frmREQSDATA.REQFRMBY.Value = oWord.ActiveDocument.Bookmarks(1).Range.Result
' Form_frmREQSDATA.REQFRMBY.Value = oWord.ActiveDocument.Bookmarks.Item(1).Result
' Form_frmREQSDATA.REQFRMBY.Value = oWord.Bookmarks(1).Value
' Form_frmREQSDATA.REQFRMBY.Value = oWord.Bookmarks(1).Result
' Form_frmREQSDATA.REQFRMBY.Value = oDoc.Bookmarks(1).Result
' Form_frmREQSDATA.REQFRMBY.Value = oWord.ActiveDocument.Bookmarks("REQFRMBY").Result
' Form_frmREQSDATA.REQFRMBY.Value = oWord.ActiveDocument.Bookmarks("REQFRMBY").Range.Value
' Form_frmREQSDATA.REQFRMBY.Value = oDoc.Bookmarks("REQFRMBY").Value
' Form_frmREQSDATA.REQFRMBY.Value = ActiveDocument.Bookmarks.Item("REQFRMBY").Range.Text
' Form_frmREQSDATA.REQFRMBY.Value = ActiveDocument.Bookmarks.Item("REQFRMBY").Range.Text

oDoc.Close
Set oDoc = Nothing
Set oWord = Nothing
DoCmd.Close acForm, "frmUpload_Requirements", acSaveNo

 

[kentover]

The code below returns
in each field.



strPath = Me.DOC_LOC
Dim oWord As Object
Dim oDoc As Object
Dim I As Integer
Dim strFLD As Stream

'Open MS-Word via OLE.
Set oWord = CreateObject("word.application")
'Open the form document.
Set oDoc = oWord.Documents.Open(strPath)

oWord.Documents(strPath).Activate
If oWord.ActiveDocument.ProtectionType <> wdNoProtection Then
oWord.ActiveDocument.Unprotect
End If

For I = 1 To oWord.ActiveDocument.Bookmarks.Count
'strFLD = ActiveDocument.Bookmarks(I).Name
Select Case I
Case 1
Form_frmREQSDATA.REQFRMBY.Value = oWord.ActiveDocument.Bookmarks(I).Range.Text
Case 2
Form_frmREQSDATA.AUDIENCE.Value = oWord.ActiveDocument.Bookmarks(I).Range.Text
Case 3
Form_frmREQSDATA.FREQUENCY.Value = oWord.ActiveDocument.Bookmarks(I).Range.Text
Case 4
Form_frmREQSDATA.PURPOSE.Value = oWord.ActiveDocument.Bookmarks(I).Range.Text
Case 5
Form_frmREQSDATA.RPTFORMAT.Value = oWord.ActiveDocument.Bookmarks(I).Range.Text
Case 6
Form_frmREQSDATA.DELIVERY.Value = oWord.ActiveDocument.Bookmarks(I).Range.Text
Case 7
Form_frmREQSDATA.PROJSUM.Value = oWord.ActiveDocument.Bookmarks(I).Range.Text
Case 8
Form_frmREQSDATA.RECSEXP.Value = oWord.ActiveDocument.Bookmarks(I).Range.Text
Case 9
Form_frmREQSDATA.LOCPREVRPT.Value = oWord.ActiveDocument.Bookmarks(I).Range.Text
Case 10
Form_frmREQSDATA.FLDSREQ.Value = oWord.ActiveDocument.Bookmarks(I).Range.Text
Case 11
Form_frmREQSDATA.CRITREQ.Value = oWord.ActiveDocument.Bookmarks(I).Range.Text
Case 12
Form_frmREQSDATA.ADDINFO.Value = oWord.ActiveDocument.Bookmarks(I).Range.Text
End Select
Next

oWord.ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

oDoc.Close
Set oDoc = Nothing
Set oWord = Nothing
DoCmd.Close acForm, "frmUpload_Requirements", acSaveNo

 

[Remou]

What happens when you try the snippets I suggested in the word document itself?

 

[kentover]

Actually nothing happened. The Debug did not start. Instead I put the code behind a button and dumped the values into a string to populate a multiline textbox.

Private Sub CommandButton1_Click()
    Dim I
    Dim strINFO
    Me.GETANSWERS.Value = ""
    For I = 1 To ActiveDocument.Bookmarks.Count
        strINFO = strINFO & "RANGE: " & ActiveDocument.Bookmarks(I).Range & vbTab & _
                " NAME: " & ActiveDocument.Bookmarks(I).Name & vbTab & _
                " BookmarkID: " & ActiveDocument.Bookmarks(I).Range.BookmarkID & Chr(10)
    Next
     
    Me.GETANSWERS.Value = strINFO
End Sub

 

[Remou]

You will find the results if a debug.print in the Immediate Window (Ctrl+G in the code window). What was the result, that is what did strINFO contain?

 

[kentover]

The bookmark names were returned as well as their ID. All of the range values showed that square. I am not sure what the square value actually is though.