Import Data Directly from Word Forms to Access Tables

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Import Data Directly from Word Forms to Access Tables

by Sean Kavanagh

Applications: Microsoft Access 2000, Microsoft Word 2000
Operating System: Microsoft Windows

When you need to gather information from people who don't have a direct connection to your Access files, a typical approach for getting the data is to have people submit paper forms, which are then manually entered into a database. You'll likely find that a number of issues complicate your attempts to streamline the process. First, there's a good chance the individuals won't have Access, since it isn't part of the standard Office suite. In addition, you can't rely on Outlook forms because you don't have control over what email clients the remote individuals are using. Collecting data through the Web seems promising, but if you aren't already equipped to do so, it may be a challenge you don't want to take on.

In spite of all these problems, you do have a viable alternative to manually entering data from hard copy forms—Word. In this article, we'll show you how to create forms in Word that store data in a way that can be easily read by Access, such as the example form shown in Figure A. After showing you how to set up and format the Word form, we'll look at how to retrieve data and save it to a table using both ADO and DAO techniques.

Figure A: Using a specially formatted Word document, you can eliminate manually entering data from hard copy forms.

Creating forms in Word

The easiest way to gather data with Word is to use its built-in form fields. Form fields let you identify a specific input location in a Word document using text boxes, dropdown lists and check boxes. Word forms are an ideal solution when data must be collected offline from your Access database, because a user can simply fill in the form fields within Word and then email the completed document to you. Since Word is preinstalled on most systems, either through Office or newer versions of Microsoft Works, there's a good chance that anyone with access to a PC can open a Word file. Once the completed form is received, VBA can be used to extract the data using the Word document's FormFields collection.

Create a sample application

To illustrate how to work with form fields, we'll walk you through setting up a simplified version of the form shown in Figure A. For our example, we'll suppose you process customer requests to sign up for certain healthcare plans. Keep in mind that for simplicity we won't take steps or include data that you probably would if you were developing a real-world application. For instance, we won't perform any checks to see if existing records conflict with data being imported from Word. We'll also store the data in a flat file format.

Start with the Access file

Before we begin working with Word, let's set up the Access table that the data will ultimately be stored in. To start, create a new Access database. You can either download the Zip file containing the completed database and the Word form from the URL listed at the beginning of this article, or you can follow the steps to create the database and form.

First, create a new database named Healthcare Contracts. Then, using the specifications shown in Table A, set up a table named tblContracts (don't worry about setting up a primary key for the table). In addition to using the specified field sizes, set the Allow Zero Length property for all of the Text fields equal to Yes. When you finish setting the properties, save and close the table.

Table A: tblContracts structure

Create the Word form

At this point, open a new document in Word so that you can set up the entry form. We won't worry about making our sample form look nice, but we'll apply some basic formatting. First, we'll add a table to help organize the input fields. To do so, click the Insert Table button on the Standard toolbar, then click and drag on the table design grid to create a 12 x 2 table. Using Figure B as a guide, click and drag the vertical lines in the table to resize the columns. Then, enter the basic field name descriptions shown in the first column.

Figure B: We'll organize our form fields with a simple table format.

Insert and format the form fields
Now we're ready to add the entry fields to our Word table. Contrary to what you might expect, you don't add form fields by choosing Insert | Field. Instead, choose View | Toolbars | Forms to display the Forms toolbar. Then, place your insertion point in the first cell of the second column in the table so that you can add the field that stores first name data. Next, click the Text Form Field button on the Forms toolbar and Word adds the field, which appears as a small gray box.

Using the same technique, add fields for all of the data through State. When you get to the ZIP row, add two fields separated by a dash. Then, add text fields for Phone, Social Security # and Birth Date. Don't worry about the remaining two items—we'll come back to the Gender and Additional Coverage data in a moment.

The text fields we've added so far are suitable for accepting data that can be passed to Access just as they are. However, we can format the fields to make the process more efficient and easier to code. We'll start by formatting the First Name field. To do so, double-click on the field to display the Text Form Field Options dialog box.

The default field options are somewhat vague. The Maximum Length is unspecified, so a person could potentially enter data that's longer than the destination Access table field. To resolve this, set the Maximum Length equal to the same length as our FirstName field, 25. Likewise, the Bookmark value is a generic name, Text1. The Bookmark entry is used as the VBA Name property that identifies the field within the FormFields collection, so replace Text1 with the more meaningful fldFirstName. Finally, although Word's form field data validation is somewhat limited, we can help ensure that data is formatted correctly by choosing Title Case from the Text Format dropdown list, as shown in Figure C. Finally, click OK to save the changes. Use the settings shown in Table B to name the remaining text fields and set the appropriate options.

Figure C: Word provides a limited amount of control over the input accepted in a form field.

Table B: Form field option settings

Simplify entry with check box and dropdown controls
In addition to the text field form control we've worked with so far, you can also use check box and dropdown list controls in Word forms. These alternative controls are still treated as members of the FormFields collection, so you can access their data just like text form fields. We'll use a dropdown list control to get Gender information and a check box to collect Additional Coverage data.

First, place your insertion point in the cell next to the Gender field label. Then, click the Drop-down Form Field button on the Forms toolbar. At first glance, the field looks just like a text field. Double-click on the field to set its options.

First, change the Bookmark value to fldGender. Next, click in the Drop-down Item text box. Word form dropdown lists automatically display the first list item in the form, which raises the potential for errors. We'll force our end users to make a selection in this field by making the first list entry a series of blank spaces. Use the [spacebar] key to insert seven blank spaces, then click the Add button to move the entry to the Items In Drop-down List listbox. Next, type Male and click Add. Finally, type Female and click Add to complete the item list, as shown in Figure D, then click OK.

Figure D: Word forms provide support for dropdown lists, reducing the possibility for errors.

The last control we'll add is the check box. Place your insertion point in the appropriate cell and click the Check Box Form Field button on the Forms toolbar. Double-click on the field and rename it fldAdditional. Then click OK. At this point, all of the form fields are set up and ready for entry, as shown in Figure E.

Figure E: The form fields appear as gray boxes, indicating where entries are accepted.

Protect and save the form
Before you distribute the form, you need to protect it. Otherwise, when users enter data in the form fields, they'll simply overwrite the fields instead of saving their data within the fields. To lock the form, click the Protect Form button on the Forms toolbar. Since we won't use the toolbar anymore, choose View | Toolbars | Forms from the menu bar to hide it.

Now, we'll save the form as a template to make it easier to fill out new forms, but you could just as well distribute it as a regular Word file. To save the template, choose File | Save As from the menu bar. Then, select Document Template (*.dot) from the Save As Type dropdown list. In the File Name text box, enter Coverage Form and click Save. Finally, close the template.

If you downloaded the file from the FTP site, unzip the file, then copy the Coverage Form template to the C: Windows\Profiles\<username>\Application Data\Microsoft\Templates folder.

Complete some sample forms

In order to test our application, we'll need some completed forms to work with. Before completing any forms, set up a folder named Contracts on your C: drive. To simplify our process, we'll assume that you store all completed contracts in the Contracts folder to make identifying what you want to import easier.

Now, let's create some samples that we can work with. To do so, choose File | New from Word's menu bar. Then, double-click on the Coverage Form icon.

You'll find that the only parts of the protected document that you can select are the form fields. Fill in the fields using whatever data you want and save the document when you've finished. Since we're using a template, you're automatically prompted to save your document under a new name. For the purpose of our example, we recommend using a simple one-word filename (such as the last name of the contact). Regardless of what you name the document, be sure you save the file to the C:\Contracts directory. Follow the same steps to create as many sample documents as you want to use to test the import process, then close the files.

Create the import procedure

The only task left is to create the code that extracts the data from your Word documents. Return to the Healthcare Contracts database and switch to the Modules sheet of the Database window. Next, click the New button to open a new module.

In order to work with Word files through VBA, we need to reference Word's object library. To do so, choose Tools | References from the VBE's menu bar. Then, select the Microsoft Word 9.0 Object Library (or the appropriate version for your system). Finally, click OK.

Now that the library reference is set, we can create the procedure. You can use the ADO procedure shown in Listing A. Note that you'll need to adjust the path specified for the cnn object variable if you created your database in a folder other than C:\My Documents. Finally, save the module as basWordImport.

Listing A: ADO procedure to import Word data

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:\Contracts\" & _
    InputBox("Enter the name of the Word contract " & _
    "you want to import:", "Import Contract")

Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(strDocName)

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\My Documents\" & _
    "Healthcare Contracts.mdb;"
rst.Open "tblContracts", cnn, _
    adOpenKeyset, adLockOptimistic

With rst
    .AddNew
    !FirstName = doc.FormFields("fldFirstName").Result
    !LastName = doc.FormFields("fldLastName").Result
    !Company = doc.FormFields("fldCompany").Result
    !Address = doc.FormFields("fldAddress").Result
    !City = doc.FormFields("fldCity").Result
    !State = doc.FormFields("fldState").Result
    !ZIP = doc.FormFields("fldZIP1").Result & _
        "-" & doc.FormFields("fldZIP2").Result
    !Phone = doc.FormFields("fldPhone").Result
    !SocialSecurity = doc.FormFields("fldSocialSecurity").Result
    !Gender = doc.FormFields("fldGender").Result
    !BirthDate = doc.FormFields("fldBirthDate").Result
    !AdditionalCoverage = _
        doc.FormFields("fldAdditional").Result
    .Update
    .Close
End With
doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Contract Imported!"

Cleanup:
Set rst = Nothing
Set cnn = 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 5941
    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

An examination of the code
After declaring the required variables, our procedure uses an input box to get the name of the Word document containing the data to import into Access. If a user closes the input box without specifying a file, or the file doesn't exist, an error of 5121 or 5174 is raised and the procedure stops after displaying a warning message. If the document is found, Access creates the object variable representing Word, appWord. Then our procedure opens the appropriate Word document, setting the object variable doc.

Once our code has set the recordset object variable rst equal to tblContracts, it simply adds a new record to the recordset and populates each of the fields with the data found in doc's FormFields collection. To retrieve the value stored in a field, instead of the FormField object, we must use the field's Result property. If any one of the specified fields isn't found in the Word document, error 5941 is raised and the process is aborted.

Running the procedure

To run the GetWordData procedure, if necessary, open the module containing the procedure and click the Run Sub/User Form button. When you do, Access displays an input box prompting you for the name of the document you want to import. Enter the name of the file in the text box, including the .doc extension, and click OK. Assuming that you entered a valid name and the document contains the appropriate field names, Access returns a confirmation message box when the import is finished. After you import a few documents, open tblContracts and examine the results.

Form field shortcomings

Notice that when we set up our Word fields we used Number types for the ZIP, Phone and Social Security data, even though the corresponding Access fields are Text data types. We did this because Word's text form fields don't provide data validation or input masks as Access does. To ensure that data conforms to your database standards, you may have to use code to format the data during the import process. In the case of the Phone and Social Security data, we compensated for the shortcoming using custom number formats. For the ZIP, we created two separate fields, which our code concatenates during the import process. Depending on how you like to approach such situations, you might choose to handle phone data the same way, breaking it into three separate entry fields. Another alternative is to handle the formatting through your VBA module, using the Format() function.

Also note that Word's check box fields behave differently than Access's Yes/No fields, even though the data in our case produced accurate results. Word uses values of 1 and 0 to represent Yes and No, whereas Access uses -1 and 0. Depending on how you're using the data, you may want to compensate for the discrepancy in your own applications.

Avoid hard copy hassles

Using hard copy forms to update a database is inherently troublesome. From an efficiency standpoint, manually entering data that someone already spent time compiling is a waste of time. If the forms are handwritten, you also risk entering information incorrectly due to the barely decipherable writing commonly found on hard copy forms. While the alternative technique for gathering information we've shown you may not eliminate hard copy forms, it should reduce the effort required in many cases.

Copyright © 2001 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.