Using Word as Remote Data Collection for Access

By Bryan Carbonnell view Bio

Demo file download sample files Print this Page

Have you ever needed to collect data from a non-Access source? This article will show you how to use Word automation to collect that data. You’ll use Word’s Fill-In Forms feature to collect non-Access data by filling in a Word form. Then, the user e-mails the form so you can import the data from the form into your Access database.

Introduction To Word Forms

The first thing you need to do is create a Word form for the user to complete. You create a Word form similarly to the way you create any other Word document, except you insert form fields in the document to give the user some place to put their data. To do so, you will need to display the Forms toolbar shown in Figure A.

Figure A

The controls on the toolbar, from left to right are as follows:

1. Text Form Field

2. Checkbox Form Field

3. Drop-Down Form Field

4. Form Field Options

5. Draw Table

6. Insert Table

7. Insert Frame

8. Form Field Shading

9. Protect Form

You’ll be using 1, 3, 4, 8, and 9 in this article. Number 1 is the Text Form Field. It works similarly to Access’ form textbox. Number 3 is the dropdown Form Field. It is similar to Access’ form Combo Box, with a couple of notable exceptions. You need to enter the values in the dropdown by hand; it can’t take a recordset as control source. As well, you are limited to 25 entries. Use number 4 to set form field properties, such as a controlling name (similar to Access’ control source), default text, text format, and other properties. Number 8, Form Field Shading toggles field shading. I recommend that you leave Form Field Shading on to provide a visual cue to the user (and yourself) where the data needs to be entered. Number 9, Protect Form enables the actual fields. Once the form is protected, is the fields are the only area in the form that accept data. None of the boilerplate text can be modified. If you feel you need to ensure the user can’t unprotect the form, and modify the boilerplate text, choose Protect Document from the Tools menu and enter a password. Now that we have quickly looked at the Forms toolbar, we will look at creating a simple form.

Creating a Word Fill-in Form

To create a simple Word fill-in form, follow these steps:

1. Create a new blank document in Word.

2. If the Forms toolbar isn’t visible, turn it on.

3. On the first line type “This is where we will enter some text:”

4. Hit Enter twice and then type: “This is the date that we typed it:”

5. Hit Enter twice and then type: “This is where we will pick one of the options from the Drop-Down:”

6. Now we need some fields to fill in. Place your insertion point at the end of the first line and then click the Text Form Field button on the Forms toolbar.

7. Place your insertion point at the end of the second line of typed text and click the Text Form Field button again. We will use the Text Form Field for both text and dates. If we were entering numbers, we would use the Text Form Field as well.

8. Now place your insertion point at the end of the third line of typed text and click the dropdown Form Field button.

Now that we have the text and the field on the form, I’ll bet you’re thinking that it’s time to protect the form and see how it works. Well, you’re almost right. If we just had Text Form Fields on the form we could do that, but first we need to format the Text Form Field that will hold the date and add selections to the dropdown Form Field.

Let’s start with the first Text Form Field that you added. Place your insertion point within the shaded field (You do have Form Field Shading on, right?) and click the Form Field Options button. You should see a dialog box that looks like the one shown in Figure B.

Figure B

Set the first Bookmark text from Text1 to txtDataEntry. This is the Form Field name that is used when automating data extraction from Word, so make the name meaningful to you. Set the second Text Form Field’s Bookmark text to dteEntered. Even though this is a text field, it will be used for a date, which is why I chose the name dteEntered. Change the Field Type to date using the Type dropdown to limit the entry in this field to a date. You can also set the Text Format to the way you want to have the data displayed. There are various built-in formats for dates. If none of them suit you, you can type in your own format.

Finally, add some data for the user to select to the dropdown . To do so, open the properties for the dropdown field. The properties dialog box should resemble the one shown in Figure C:

Figure C

To add entries, type the text you want to appear in the Dropd-Down Item control, and click the Add button. You can add up to 25 items To remove an entry, select that entry in the Items in Drop-Down List control, and then click the Remove button. For now, just add a few items, so you can see how it works. The last thing you need to do before you leave this dialog is to give the dropdown field a meaningful name, such as cboStuff.

Now you’re ready to test the form by clicking the Protect Form button on the Forms toolbar. It’s the rightmost button, the one that looks like a padlock. Once the form is protected you can only enter data in the provided ffields. If you’ve turned on Form Field Shading (as I recommended earlier) you’ll see grey boxes. These are the locations where you’ll enter data. Try now to enter some data. As you do so, remember that you can enter data values it in different ways, as follows:

· You can enter just numbers, 18-08-2002.

· You can enter months in short text format, 18-Aug-2002.

· You can enter full month names, August 18, 2002.

It doesn’t matter how the data is entered, Word will format it by referring to the Text Format in the Text Form Field Options dialog box. Now that we have seen how to create a simple Word Form, lets move on to getting data out of a more complex form and into Access.

Getting Data from a Word Form

The basic steps for getting data from a Word Form into Access are as follows:

1) Create a new or reuse an existing instance of Word.

2) Open the Word Form, which is just a Word Document.

3) Get the value of the form field.

4) Validate the data if you wish.

5) Place the data into the appropriate control in your Access Form.

6) Repeat steps 3, 4 and 5 until all the fields have been read.

7) Close the document.

8) Close or release the Word instance.

It’s a very simple process. To accomplish steps 1 and 2 read parts I (link to part I) and II (link to part II) of this articles . This portion of the code is provided below and in the basWordImport module of the demontration database:

'Get Reference to Word Application
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
'An error indicates there was no Word instace open
' so we need to creae a new Word Instance
If Err.Number = 429 Then
    Set objWord = CreateObject("Word.Application")
    'Set flag to indicate we created a New Word Instance
    bolCreated = True
End If

'Enable Built in Error handling
On Error GoTo 0
'Show Word Instance
objWord.Visible = True
'Open selected File
Set doc = objWord.Documents.Open(frm.txtFilename.Value, False, True)

The only thing that needs a bit of explanation here is the frm.txtFilename.Value parameter in the last line above. This value is the fully qualified path to the Word form. It ‘s pulled from one of the two import wizards in the database (more on that in just a minute). Other than that, it’s the same code used in the earlier articles.

One thing I like to do is add a custom document property to the Word Form Field document. I do this so I can check to see if the document that the user picked is a valid Word form. There’s nothing like trying to import data from a Word form, which has no form fields on it! The following code provides this quick check:

'Check to see if the Custom Document Property is set
'Need some error handling in case custom document property doesn't exist
On Error Resume Next
strProperty = doc.CustomDocumentProperties("Project")
On Error GoTo 0

You must turn off the error-handling because if the Custom property doesn’t exist, it returns an error. We really don’t care about the error; we just need the value of the custom property. In this case, the Custom Property’s name is Project. This property is set by opening a Word document, selecting Field | Properties…, clicking the Custom Tab, and selecting Project, from the Name List box. Enter a value in the value textbox and click the Add button. Once there’s a property value the following code checks it:

If strProperty = "M2M-PartIII" Then
    ‘Code for the import goes here
Else
    'The selected file was not in the correct format, so we can't import it
    MsgBox "This document is not the correct format to import." _
            & vbCrLf & "" _
            &vbCrLf &"Please select a different document." _
            , vbCritical, "Incorrect Document Format"
    'If we get here, the import failed so set the return value to False
    fSimpleImportWordData = False
End If

If the value of the property isn’t correct for this import, an appropriate message is presented to the user, and then cancels the import. If it’s the correct property, move on to the import task.

The Simple Import Method

The simple method is basically a loop that gets the Word Form Field, matches its name to the Access form field name and copies the value of the Word Form Field into the appropriate Access form field. The basic steps are as follows:

1) Get Value of Word Form Field.

2) Assign the value of the Form Field to a control in Access.

3) Do Step 1 & 2 until there are no more Word Form Fields to import.

Here is the code that imports:

Dim ffl as Word.FormField

'Loop trough the Word FormFields and populate Access Form fields
'  (of the same name) with the data from Word
For Each ffl In doc.FormFields
    'Populate Fields
    frm.Controls(ffl.Name) = ffl.Result
Next

The important line in this code is

frm.Controls(ffl.Name) = ffl.Result

This statement assigns the value of the Word Form Field (ffl.Result) to the control on the Access form with the same name (frm.Controls(ffl.Name)). This process works without fail if there’s an Access form field for each Word form field. If the two pieces don’t match exactly, your code will return an error. A more stable approach is to use the following code:

On Error Resume Next
'Loop trough the Word FormFields and populate Access Form fields
'  (of the same name) with the data from Word
For Each ffl In doc.FormFields
    'Populate Fields
    frm.Controls(ffl.Name) = ffl.Result
    'If there was an error then put the FormField Name in the
    ' General Notes text box in Access
    If Err.Number <> 0 Then
        frm.txtMissedField = frm.txtMissedField & vbCrLf _
            & ;ffl.Name & " - " & ffl.Result
    End If
    'Clear an error that occurs
    Err.Clear
Next
On Error GoTo 0

This code is similar to the previous example; the only difference is in the additional error-handling to catch extra fields in Word. Z9The extra code is the On Error statements and the If Err.Number <> 0 block.) The additional code catches any non-zero errors, which would indicate that the Access control doesn’t exist.

If there happens to be an error, the value, and name, of the Word Form Field is appended to a catch-all note textbox in Access. In production code, I wouldn’t add the Form Field Name to the notes textbox, but leaving it in for development is a good way to ensure that you have all your fields captured.

(The entire code for doing a simple import from Word can be found in the fSimpleImportWordData procedure in the basWordImport module.)

The limitation of this technique is that you can’t do any data validation before you place the data in the form. Therefore, you’ll need some data validation code in the form’s BeforeUpdate event. The inability to do data validation before assigning the data to the Access controls can cause issues with list box and combo box controls, such as Not In List errors.

The Complex Import Method

The more complex import method follows the following steps:

1) Get Value of Word Form Field.

2) Do Data Validation.

3) Assign the value of the Form Field to a control in Access.

4) Do Step 1, 2 & 3 until there are no more Word Form Fields to import.

There are two differences in the complex import. First, step 2, data validation, is new. Second, assign the value of specific Word Form Fields to specific Access controls. Let’s begin with assigning values to Access controls. You’ll still loop through all the Word Form Fields, but instead of using the following code

  frm.Controls(ffl.Name) = ffl.Result

you’ll use a Select Case structure to assign the values, as follows:

With frm
    For Each ffl In doc.FormFields
        Select Case ffl.Name
            Case "txtShipToName"
                .txtShipToName = ffl.Result
            Case "txtShipToAddress"
                .txtShipToAddress = ffl.Result
            Case "txtShipToCity"
                .txtShipToCity = ffl.Result
            Case "txtShipToProv"
                .txtShipToProv = ffl.Result
‘... More Case conditions would go in here
‘      One for each Word Form Field
            Case Else
                ‘Catch all incase we missed a field
                .txtMissedImport = .txtMissedImport & _
                    vbCrLf & ffl.Name & " = " & ffl.Result
        End Select
    Next
End With

The code checks the name of the Word Form Field and then assigns that name to a specific Access control. The names of the controls just happen to be the same as the Word Form Field names, but they don’t have to be, since you explicitly name the control.

Now let’s add the data validation. Let’s take the Ship To City data, which is in the Word Form Field txtShipToCity. Currently the data is copied into a text box on the Access form. Suppose we want to change it to a combo box (cboShipToCity), to shipped to cities. If the user misspelled the city (LA instead of the Los Angeles) the code returns error number 2237. (The text you entered isn’t an item in the list). You could just trap for that error and ignore it, but a more elegant solution is to validate the data first.

Here’s one possible solution. First, check to see if there’s an exact match in the lookup table. If there is, the data is used as is. If there isn’t, use a Soundex routine for text. (I use the one from the VBA Developer’s Handbook to get a close match and use that close match as the data that gets put into the control. I also display what the original value was in a label directly above the control, so the user can see what was entered and what the system has done.)

Lets go back to the txtShipToCity as an example. The code that would be used is as follows:

‘Check to see if the data is actually in the Lookup table
strSQL = “SELECT * FROM tlkCity WHERE fldCityName = ‘” & ffl.Result & “’”
Set rst = db.OpenRecordset(strSQL)
If rst.RecordCount >0 THEN
    ‘There was a match, so we will use it
    cboShipToCity = rst.Fields(“fldCityID”)
    rst.Close
    set rst = Nothing
else
    ‘Use Soundex to get close match
    rst.Close
    Set rst = Nothing
    ‘Get the first character of the Data
    StrTemp = left$(ffl.Result, 1)
    For intLoop = 0 to cboShipToCity.ListCount – 1
        ‘Check to see if the first characters OF THE TEXT are the same
        ‘  no sense in comparing words that don’t start with the same letter
        ‘  The combo cboShipToCity has 2 cloumns, 
        ‘  the first is an Autonumber PK (hidden)
        ‘  the second is the City Name (visible)
        If Left(cboShipToCity.Column(1, intLoop), 1) = strTemp Then
            ‘Compare the data entered to the TEXT data in the City Combo
            ‘  using a Soundex Routine from the VBA Developer’s Handbook
            If dhSoundsLike(ffl.Result, _
              cboTrngItem.Column(1, intLoop)) = 4 Then
                ‘A return of 4 from the dhSoundsLike function is the highest
                ‘  “score” that the function can return
                ‘Got the first Soundex match
                cboShipToCity = intLoop
                ‘Have a match, so we can exit loop
                Exit For
            End If
        End If
    Next
End If
‘Change caption of data label to show original data
lblOriginalShipToData.Caption = “Original Data form Word Form: “ & ffl.Result

If you read the code closely, you’ll notice that it sets the value of the combo box only if there is a perfect Soundex match. It leaves the control blank if there isn’t. This works quite well for my purposes. You may wish to set the value of the combo to the highest match, whether that be a 0, 1, 2, 3 or 4 (the only values that are returned by the dhSoundsLike function, the higher number the better the match is).

This data validation code is not included in the demonstration database, since it’s so specific to the data you’re validating. Just remember to place the validation code before you assign the value to a control.

Summing it up

This article presents the basics of importing data from Word. It sounds and looks more complicated than it really is. There are a couple of things to keep in mind:

· Decide if you’re going to use the simple or complex import method.

· If you have extra fields in your Word Form, you’ll need a catch-all field for storing the data (or you could just ignore them).

If you want to have a look at Word’s Object model, you can look at these pages on Microsoft’s MSDN site.

Word 2000:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeomg/html/deovrmicrosoftword2000.asp?frame=true

Word XP:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modcore/html/deovrMicrosoftWord2000.asp?frame=true

Please contact the author at carbonnb@sympatico.ca to ask questions or report errors.