Importing data from Word form content control into Access

[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.