Fill in Word Forms Using Information Stored in Access
Fill in Word Forms Using Information Stored in Accessby 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 controloften 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 fieldsThere 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 directionwe'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
Table A: DAO substitutions
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
|ADO statements||DAO statements|
| || |
| ||(delete this statement)|
| || |
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
On Error Resume Next statement allows the procedure to move to the next
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
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
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:
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
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)
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
create a recordset to determine the name associated with the ReportsTo value.
With doc...End With section of code is what actually updates the
Word form fields. We use the previously assigned names with the
collection to single out each field in the Word document. Then, we simply set
Result property equal to the appropriate variable or value from
the Employees form. Sending a
Null value to a
result in an error, so we use the
Nz() function, which substitutes a
zero-length string in the event that an Access field is
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: firstname.lastname@example.org.