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("FullName") = 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("FullName")=doc.FormFields("txtYourName ").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("FullName") = doc.FormFields("YourName").Result

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.

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.Text" 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

[tsnave]

Here is my modified code. Works great. I do have 2 checkboxes that I need if they are checked or not.

Sub GetWordData()
 
Dim cc As ContentControl
Dim fc As Field
Dim ccInfo As String
Dim YourName As String
Dim YourAddress As String
Dim YourPhone As String
Dim Male As CheckBox
Dim Female As CheckBox
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)
 
For Each cc In doc.ContentControls
        ccInfo = "<> ID= " & cc.Id & " Title = " & cc.Title & " Text = " & cc.Range.Text & vbCrLf
        Debug.Print ccInfo
        Select Case cc.Title:
            Case "YourName"
                YourName = cc.Range.Text
            Case "YourAddress"
                YourAddress = cc.Range.Text
            Case "YourPhone"
                YourPhone = cc.Range.Text
            Case "Male"
                Male = cc.Range.Text
            Case "Female"
                Female = cc.Range.Text
        End Select
 
 
Next
 
 
Set cnn = CurrentProject.Connection
 
rst.Open "FormData", cnn, adOpenKeyset, adLockOptimistic
 
With rst
    .AddNew
 
 
    rst.Fields("FullName") = YourName
    rst.Fields("Address") = YourAddress
    rst.Fields("Phone") = YourPhone
    rst.Fields("Male") = Male
    rst.Fields("Female") = Female
 
    rst.Update
    .Close
End With
 
 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.Number
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

The Male and Female fields are the checkboxes. The other forms that I need to import have around 60 or so checkboxes.
Thank you for all your help. This is my first time with VB and I have learned a lot from all your help.

[tsnave]

I got the checkboxes figured out and here is the finished working code:

Sub GetWordData()
 
Dim cc As ContentControl
Dim fc As Field
Dim ccInfo As String
Dim YourName As String
Dim YourAddress As String
Dim YourPhone As String
Dim Male As String
Dim Female As String
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)
 
For Each cc In doc.ContentControls
        ccInfo = "<> ID= " & cc.Id & " Title = " & cc.Title & " Text = " & cc.Range.Text & vbCrLf
        Debug.Print ccInfo
        Select Case cc.Title:
            Case "YourName"
                YourName = cc.Range.Text
            Case "YourAddress"
                YourAddress = cc.Range.Text
            Case "YourPhone"
                YourPhone = cc.Range.Text
            Case "Male"
                If cc.Checked Then
                 Male = "Yes"
                Else: Male = "No"
                End If
            Case "Female"
                If cc.Checked Then
                Female = "Yes"
                Else: Female = "No"
                End If
        End Select
 
 
Next
 
 
Set cnn = CurrentProject.Connection
 
rst.Open "FormData", cnn, adOpenKeyset, adLockOptimistic
 
With rst
    .AddNew
 
 
    rst.Fields("FullName") = YourName
    rst.Fields("Address") = YourAddress
    rst.Fields("Phone") = YourPhone
    rst.Fields("Male") = Male
    rst.Fields("Female") = Female
 
    rst.Update
    .Close
End With
 
 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.Number
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

I can't thank you enough for all your help.

[zmbd]

Happy that we have that working.
I should have also pulled the field type with the code in Post#10 - didn't think of it at the time.

Thank you for posting your final code. I'm sure that alot of people will find this usefull! The built in form for getting information via email leaves much to be desired and if one does not have MS-Infopath installed, then one is stuck with it. By using the word document, the form can be user friendly and with a little code in the controls even the data can be validated.

Two things of note:
1) Post#17:Lines 32 and 33 These two lines are there only for debug work; thus, you can either comment them out or remove them.
2) Using two fields to store the "Sex" information may not be the best idea. "Assuming" that an individual is filling this form out for themself and then you shouls only need one field in the database to store the information.
-- Based on the assumption:

- Modify the data base to have only one field to hold the sex of the respondant.
- Use the exsiting code to pull the information from the check box controls.
- I would then have a little logic to assign either "M," "F," or "X" to a new variable, I would then use this variable to assign the value to the record field and also pop-up an error message that there is a Mutant running loose :)
- You could also log the file name etc... to an errors table.
- With the "X" you can build a query that pulls all records with a Sex = "X" and attempt to connact the respondant.

Then again, the question could be something where checking both boxes is logical... depends on the question; and more than likely still breaks the rules of "normalization"...

...which is something...

You should take a few moments to read about: > Database Normalization and Table Structures.
I'm still looking for a good tutorial site, my favorit went down due to i-frame issues.