Fill in Word Forms Using Information Stored in Access

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.

Fill in Word Forms Using Information Stored in Access

by Sean Kavanagh

Applications: Microsoft Access 97/2000/2002, Microsoft Word 97/2000/2002

Although Access reports are flexible and relatively easy to create, they're not always suitable for every task. For example, your company may internally use manual forms that can easily be filled with data from an existing Access database. Your natural reaction is probably to create an Access report that replicates the hardcopy form, but a number of hurdles may prevent you from doing so.

For instance, suppose that many other people have a continued need to fill the forms in manually. A consistent appearance between forms filled in manually and electronically is probably a requirement, so you could be looking at a lot of work to make the distinction between your Access report and the original form unnoticeable. In addition, the design of the forms is likely beyond your control—often maintained by someone using Word. Any time a form is modified, even cosmetically, you need to make changes to your Access objects. Fortunately, if the original forms are stored in Word, you can take advantage of the design work that's already been done. In this article, we'll look at how to populate Word documents with data from Access.

Updating Word form fields

There are several ways to go about moving data from Access to Word. This article will serve as the first in a series that looks at a few approaches. For now, we'll use form fields. In the April 2001 article "Import data from Word forms to simplify data collection," we showed you how to retrieve data from a Word document and save it in an Access table. The technique involved using named form fields in a Word document. Now, we'll move the data in the opposite direction—we'll populate a Word file's form fields with data from Access.

Set up the sample database

To simplify setup for our examples, we'll use objects from the Northwind sample database. So as not to alter the original Northwind files, we'll import the required objects into a new Access database. Create a new blank database and choose File | Get External Data | Import from the menu bar. Then, browse to and select the Northwind database and click Import. When the Import Objects dialog box appears, select Employees on the Tables sheet. Next, switch to the Forms tab and again select Employees. Finally, click OK.

Create the Word document

We'll start by taking a look at how to create a Word document that uses form fields. Open a new Word document and choose View | Toolbars | Forms to display the Forms toolbar. For our example, we'll use the basic layout shown in Figure A, which you can re-create or download from the FTP site listed at the beginning of this article. Although our example isn't exactly true-to-life (you probably wouldn't include Salutation information on such a form), it will serve to illustrate basics of our technique. You don't need to worry about all of the minor formatting we've done, but to get the basic table layouts, choose Table | Insert | Table from the menu bar, set Number Of Columns to 3 and click OK.

Figure A: We'll programmatically update this Word table with data from Access.

Once the skeleton of the form is laid out, click in the table cell beneath the Salutation heading. The first form field we'll set up provides users with a dropdown list. Click the Drop-Down Form Field button on the Forms toolbar and Word inserts a field placeholder into the table cell.

Now, we'll configure the field to specify the contents of the dropdown list. To do so, double-click on the field to display the Drop-Down Form Field Options dialog box. Then, click in the Drop-Down Item text box, type Mr. and click Add. Repeat the process to add Ms. to the item list.

To work with form fields using VBA, you can refer to the name specified in the Bookmark text box. To simplify working with Word's form fields, we'll assign field names that are more meaningful than defaults like Dropdown1. Rename the field fldSalutation and click OK.

We'll use text boxes for the remaining form fields. Click in the table cell beneath the Name heading and then click the Text Form Field button. Using the previous steps, rename the field fldName. Create text box fields for the Title, Hire Date and Approved By cells, naming each field using the previous conventions.

Once you've set up the fields, make a minor change to the fldHireDate field's settings. Display the options dialog box for that field and change the Type setting to Date. Then, from the Date Format dropdown list, choose the MMMM d, yyyy option. Finally, click OK.

The last step you need to take in setting up your form is to protect it so that the fields don't get deleted when a user types in the table cells. To do so, click the Protect Form button. You'll notice that Word disables several toolbar buttons to protect the structure of the form, as shown in Figure B. Then, save your document as Salary Change Form and exit Word.

Figure B: Once the form is protected, users can manually add information without destroying the form fields.

Updating the form fields from Access

You currently have a form that users can open and enter data directly into using Word. Now, let's create a procedure that lets you populate a copy of the form using data from the Employees table in the database you created. To run the procedure, we'll add a button to the Employees entry form that generates a filled Word form using data from the current record.

At this point, select the Employees form in the Database window. Open it in Design view and, ensuring that the Control Wizards button isn't selected, add a command button anywhere on the Detail section of the form. Rename the button cmdPrintForm and change the caption to Print Form.

Click the Code button to open the VBE. You must now set up a reference to the Word object library. To do so, choose Tools | References from the menu bar. Then, select the Microsoft Word 9.0 Object Library check box (or whatever version is appropriate) and click OK.

We'll store the path to the Word form using constants. In the General Declarations section of the module add the following:

  Const DOC_PATH As String = "C:\My Documents\"
Const DOC_NAME As String = _
	"Salary Change Form.doc"

Make any required changes if your Word file is stored in a different location.

Next, select cmdPrintForm from the Object dropdown list. At the insertion point, add the code shown in Listing A. If you're using Access 97, refer to Table A to make the appropriate DAO substitutions. When you've finished, close the VBE, return to the Employees form and switch to Form view. Finally, save the form./

Listing A: Code to populate Word form fields

  Dim appWord As Word.Application
Dim doc As Word.Document
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strReportsTo As String

On Error Resume Next
Set appWord = GetObject(, "Word.application")
If Err = 429 Then
	Set appWord = New Word.Application
	Err = 0
End If

With appWord
	Set doc = .Documents(DOC_NAME)
	If Err = 0 Then
		If MsgBox("Do you want to save the current document " _
			& "before updating the data?", vbYesNo) = vbYes Then
				.Dialogs(wdDialogFileSaveAs).Show
		End If
	doc.Close False
	End If

	On Error GoTo ErrorHandler
    
	Set doc = .Documents.Open(DOC_PATH & DOC_NAME, , True)
	Set rst = New ADODB.Recordset
	
	If Not IsNull(Me!ReportsTo) Then
		strSQL = "SELECT [FirstName] & "" "" & [LastName] AS Name FROM " _
			& "Employees WHERE [EmployeeID]=" & Nz(Me!ReportsTo)
		rst.Open strSQL, CurrentProject.Connection, _
			adOpenStatic, adLockReadOnly
		If Not rst.EOF Then
			strReportsTo = Nz(rst.Fields(0).Value)
			rst.Close
		End If
	End If

	With doc
		.FormFields("fldSalutation").Result = Nz(Me!TitleOfCourtesy)
		.FormFields("fldName").Result = Nz(Me!FirstName & " " & Me!LastName)
		.FormFields("fldTitle").Result = Nz(Me!Title)
		.FormFields("fldHireDate").Result = Nz(Me!HireDate)
		.FormFields("fldApprovedBy").Result = strReportsTo
	End With
	.Visible = True
	.Activate
End With

Set rst = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub

ErrorHandler:
MsgBox Err & Err.Description 

Table A: DAO substitutions

Examining the code

Before testing the procedure, let's look at some of the key parts of it. The statement:

  Set appWord = GetObject(, "Word.application")

attempts to set the appWord object variable to a currently running instance of Word. If Word isn't open, error number 429 is raised. However, our previously used On Error Resume Next statement allows the procedure to move to the next statement:

  Set appWord = New Word.Application

which creates a new instance of Word.

The next section of code works with the doc object variable. We first try to set doc equal to an open instance of the Salary Change Form document. If the document is already open, then Err will equal 0 and we prompt the user to save the document in its current state. If the user clicks Yes in response to our prompt, we display Word's Save As dialog box using the statement

  .Dialogs(wdDialogFileSaveAs).Show

The user can then save a copy of the file under a different name. Finally, we close the original Salary Change Form document without saving any changes using the statement:

  doc.Close False

There are a number of reasons why we initially check whether Salary Change Form is open. If you attempt to open a specific Word file and the file is already open, Word automatically assumes you want to work with the existing instance of the document. This means that the original data in the document will be replaced when our procedure updates it with data from the current record. This may or may not be what you want. We're assuming that you may want a saved version of the populated Word file, but if you're simply creating and printing forms that don't need to be electronically kept, you might forego this effort.

You also may be wondering why we specifically close the existing doc object only to set it equal to another instance of the same Word file a few statements later. We did this as an easy way of ensuring that we update a clean version of the form each time. Since the same doc object would otherwise be used each time the form is updated, it's possible that data from one record could carry through to another in the form. For instance, you might have a field that's conditionally updated for one record. The next record you run the procedure for might not meet the condition, so the field won't be updated, leaving the data from the previous record intact. Although our approach to solving the problem is simple, you can also address it by programmatically clearing each field before you update.

When we do open a new instance of Salary Change Form using the statement

  Set doc = _
	.Documents.Open(DOC_PATH & DOC_NAME, , True)

we include True in the last argument to open the file as Read-Only. We do this simply to provide extra insurance that the user (or our procedure) won't later accidentally save over the original copy of the file. Once we've ensured that doc is set to a new instance of Salary Change Form, we check whether the ReportsTo field is Null. We're arbitrarily using the ReportsTo field to supply the data for the Approved By cell in our Word table. However, the Employees table field holds an EmployeeID value, not a name. If ReportsTo isn't Null, we create a recordset to determine the name associated with the ReportsTo value.

Finally, the With doc...End With section of code is what actually updates the Word form fields. We use the previously assigned names with the FormFields collection to single out each field in the Word document. Then, we simply set each field's Result property equal to the appropriate variable or value from the Employees form. Sending a Null value to a FormField object's Result will result in an error, so we use the Nz() function, which substitutes a zero-length string in the event that an Access field is Null.

Testing the procedure

At this point, click the Print Form button. After a few moments you should see a Word document based on the data from the form's current record, as shown in Figure C. Now, switch back to Access and experiment with some other records. It's worth pointing out that Word's form fields are fairly accommodating to your data. For instance, if you print a form for Andrew Fuller you'll see that Dr. appears in the Salutation cell, even though that choice wasn't available in our original form dropdown list. Also, Word automatically displays the Hire Date data using the specified date format without requiring any manipulation on our part.

Figure C: Access uses Automation to open a Word document and populate it with data from the database form's current record.

Moving beyond forms

Form fields provide an easy way to identify where Access information should be inserted into a Word document. However, not everyone creates their Word-based forms using form fields. We'll follow up on this article with a look at how to handle creating Word documents based on existing templates.

© 2001 Element K Journals, a division of Element K Press LLC ("Element K"). All rights reserved except for the right to view this site using a web browser and to make private, noncommercial use hereof. Element K and the Element K logo are trademarks of Element K LLC. The content published on this site ("Content") is the property of Element K or its affiliates or third party licensors and is protected by copyright law in the U.S. and elsewhere. This means that the right to copy and publish the Content is reserved, even for Content such as tips and articles made available for free, none of which may be copied in whole or in part or further distributed in any form or medium without the express written permission of Element K. Questions or requests for permission to copy or republish any content may be directed to: contentreuse@elementk.com.