Importing data from Word form content control into Access

[tsnave]

I am trying to take the user input from a Word form and place it into an Access DB. I have the document and database in the same folder on my C drive. I am getting an error at :

rst.Fields("Ful lName") = doc.FormFields( "txtYourName"). Result

It is not getting the data from the content control.

Here is all of the of the code:

Sub GetWordData()


Dim appWord As Word.Application
Dim doc As Word.Document
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean


On Error GoTo ErrorHandling



strDocName = "C:\SampleForm\" & _
    InputBox("Enter the name of the Word document you want to import:", "Import document")
     
Set appWord = GetObject(, "Word.Application")
appWord.Visible = True

Set doc = appWord.Documents.Open(strDocName)

Set cnn = CurrentProject.Connection

rst.Open "FormData", cnn, adOpenKeyset, adLockOptimistic


    rst.AddNew
    
    
    rst.Fields("FullName") = doc.FormFields("txtYourName").Result
    rst.Fields("Address") = doc.FormFields("txtYourAddress").Result
    rst.Fields("Phone") = doc.FormFields("txtYourPhone").Result
    rst.Fields("Male") = doc.FormFields("txtMale").Result
    rst.Fields("Female") = doc.FormFields("txtFemale").Result
    
    rst.Update
    rst.Close


 doc.Close
 
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Request Imported"

Cleanup:
Set rst = Nothing
Set doc = Nothing
Set appWord = Nothing

Exit Sub
ErrorHandling:

Select Case Err
Case -2147022986, 429
    Set appWord = CreateObject("Word.Application")
    blnQuitWord = True
    Resume Next
    
Case 5121, 5174
    MsgBox "You must select a valid Word Document." _
    & "No data imported.", vbOKOnly, _
    " Document Not Found"
    
Case 5491
    MsgBox "The document you selected does not" _
    & " contain the required form fields." _
    & " No data imported.", vbOKOnly, _
    " Fields Not Found"
    
Case Else
    MsgBox Err & ": " & Err.Description
    
End Select
GoTo Cleanup
End Sub

Any suggestions would be great. Thanks

[zmbd]

We can not do the basic trouble shooting for you; however,
if you will do the following, it will help us to help you:

As the line containing:

rst.Fields("FullName") = doc.FormFields("txtYourName").Result

may not be the actual source of the error:

Please place a STOP command at Line 11 and comment out Line 12

Once done
Please execute your code.
Your code will stop at line 11 on the STOP command.
Press [F8] slowly until you reach the line where the error occurs.

Please post the Line # where the error occurs
Please post:
The EXACT ERROR
Title
Number
Description.

Please do not omit or paraphrase any of the error message.

[tsnave]

When I get to the line:
rst.Fields("Ful lName")=doc.For mFields("txtYou rName").Result
it sends me to the error handling:
Case -21447022986, 429 section. I kept scrolling through and got error 5941: The requested member of the collection does not exist.

Thank you for your response

[zmbd]

You did not comment out Line12
Place a single quote ( ' ) before the entry in that line.
Then run again

I kept scrolling through and got error 5941: The requested member of the collection does not exist

This leads me to a mis-named control or field.
Which line errored with that?

[tsnave]

I commented out line 12 and the error occurs at:
rst.Fields("Ful lName") = doc.FormFields( "YourName").Res ult

I took out the txt in front of YourName so it would match the content control exactly.

Should I change FormFields to ContentControl?

[zmbd]

same error?
Please post the error Title, Number, and text.

[zmbd]

Which version of Office (or Access and Word) are you using?
If in 2007 or 2010 are you using the "classic" field controls or the newer text control?

[tsnave]

I am getting the same error and I am using Access and Word 2010. Also the "classic" field controls.

[zmbd]

PLEASE
Post THE TITLE
the ERROR NUMBER
and THE TEXT

[zmbd]

TsNave:

Make a copy of the word document.
In the copy of the word document please insert a standard code module.
Copy and past the following code block into the document.
Run the code
Follow the instructions.

Sub GetCCInfo()
Dim cc As ContentControl
Dim fc As Field
Dim ccinfo As String

' Iterate through all the content controls in the document
Debug.Print ">>>Cut and paste - Start<<<"
If ActiveDocument.ContentControls.Count <> 0 Then
    Debug.Print "Start of controls, Count = " & ActiveDocument.ContentControls.Count & vbCrLf
    For Each cc In ActiveDocument.ContentControls
        ccinfo = "<> ID= " & cc.ID & " Title = " & cc.Title & " Tag = " & cc.Tag & " Text = " & cc.Range.Text & vbCrLf
        Debug.Print ccinfo
    Next
Else
    Debug.Print "Start of controls, Count = 0"
End If
If ActiveDocument.FormFields.Count <> 0 Then
    Debug.Print "Start of Fields, Count = " & ActiveDocument.FormFields.Count & vbCrLf
    For Each fc In ActiveDocument.Fields
        ccinfo = "<> Index = " & fc.Index & " result = " & fc.Result.Text & vbCrLf
        Debug.Print ccinfo
    Next
Else
    Debug.Print "Start of fields, Count = 0"
End If

Debug.Print ">>>Cut and paste - end<<<"

MsgBox "Please open the VBA editor and press <ctrl><g>" & vbCrLf & "Please cut and paste the indicated contents to the thread"
End Sub

[tsnave]

That did pull the data from the form. Thank you very much. Now, I just got to put that into Access

[tsnave]

>>>Cut and paste - Start<<<
Start of controls, Count = 5

<> ID= 261426431 Title = YourName Tag = YourName Text = Todd Snavely

<> ID= 846142673 Title = YourAddress Tag = YourAddress Text = 1234 Main St.

<> ID= 2918279744 Title = YourPhone Tag = YourPhone Text = 555-5555

<> ID= 1014266676 Title = Male Tag = Male Text = ?

<> ID= 3052641625 Title = Female Tag = Female Text = ?

Start of controls, Count = 0
>>>Cut and paste - end<<<

[zmbd]

And that explains why you are getting the error that you are receiving - and exactly what I expected to see.

You are not using the "Classic" or "Legacy" form controls that are hold over from Word/Office 2003. You are using the new Content Controls introduced in 2007/2010.
[imgnothumb]http://bytes.com/attachment.php? attachmentid=71 09[/imgnothumb]
The Rich and Plain text controls are not part of the fields collection as you are attempting to refer to them in the vba
Note that I'm in design mode here so that you can see the tags on the new style controls.

So, you have a few options.
- We can refer to your controls as is via index.
- We can refer to your controls via the Title (the part -that is seen in normal view by the users when they enter the control for data entry.
- We can refer to the controls via the tag property.
all of which require some re-writing of your code.

OR

- You can redo your word document and use the legacy controls. Doing this you should be able to use your code as is, perhaps a tweak or two for syntax.

your call

[tsnave]

Thank you for all your help. I am going to have to refer to the title of the content control. This project was given to me by a professor who already has 370+ documents already filled out with this type of controls and need the data imported into Access.

[zmbd]

The title must be UNIQUE!

Two ways to code this (and I will leave the code writing to you :-) )...

One is the select by title method. It is ok to use, but a but fuddy for my taste and if something changes in the document such as the title actually used, then it makes the coding more complex.

Therefore, in this case let's do the loop method as I've already provided in the code Post#10-lines 10 thru 13. and modify the code you posted in post#1.
-->insert and modify your code so that:
-a- Set up variables to hold your values from the documents and for the references you'll need to use the Post#10-lines 10 thru 13. code in your current code module.
-b- Change the "ActiveDocument " object refered to in Post#10-line 10 to your open document object "doc" object refered to on post#1-line 22
-c- Use a select case statement against the value returned by "cc.title" to then return the value from "cc.Range.T ext" and assign it to the correct variable that you set up "-a-" keeping in mind that Uppercase and Lowercase are not the same in the comparisons so use something like the lcase() function to format "cc.title" correctly.
-d- make sure you validate your variables either within step -c- or after, if missing or wrong datatype the correct it now.
-e- now take the assigned variables and use those in the code post#1-Lines 32 thru 36
-f- make sure you have the appropiate error traping setup to handle any errors encountered!

Note that in step -c- if you run into a document where the title of the control is slightly off, then you only need to add that variation to the "Case" for the correct variable assignment.

You might also want to modify the If-Then statment regarding the count so that if you do happen to have a document without any content controls you will not receive an error.

Please post back your completed code, even if it works, so that others in this situation can benifit from our efforts.

Given that you have so many documents to handle, you may also wish to take a look at the File open dialog article in our insights section!!!
(*)Microsoft Access / VBA Insights Sitemap \
(*)Article # 29 Select a File or Folder using the FileDialog Object