Access reports provide a flexible and easy-to-use tool for sharing data, but sometimes, it’s the wrong tool. For instance, you may find that you can’t exactly reproduce an existing paper form in Access, whereas you can in Word. Now, you might not care what the report (paper form) looks like, but perhaps management does. When this is the case, you may find it necessary to transfer Access data to Word forms. The good news is that Word and Access play well together.
Note:This information is also available as a download, which includes text files containing the VBA code from Listing A and Listing B.
The automated solution requires three pieces:
- A method of identifying the Access data you want to transfer
- A bit of VBA code to automate the process
- A Word document that represents the form you need to fill.
We’ll work with existing data in the Northwind database (the sample database that comes with Access). Specifically, the example will transfer data from the Customers table, via a form, to the Word document shown in Figure A.
Figure A: The highlighted areas are text fields.
Creating the Word form
The Word form isn’t as difficult to create as it might look. The example form consists of five simple tables that contain form fields. In this context, a form is a document that contains fill-in blanks or form fields, in which you enter information. A field is a predefined cell for entering and storing information.
The form in Figure A uses five two-row tables to simulate a form. You don’t need the tables, but they help organize and manage the data. To insert a table, choose Insert from the Table menu and then choose Table. In the resulting Insert Table dialog box, enter the appropriate number of columns and rows (see Figure B). To create this form, you’ll need five tables as follows:
| Columns | Rows |
| 2 | 2 |
| 2 | 2 |
| 1 | 2 |
| 4 | 2 |
| 2 | 2 |
Figure B: Identify the number of rows and columns in each table.
The example form also uses shading and text in the first row to distinguish between the heading and actual information. You can skip the formatting if you like; it isn’t critical to the technique. Alter the width of each cell column, using Figure A as a guide, if you like.
The example Word document contains a matching field for each column in the Northwind’s Customers table. You don’t have to accommodate each field; transfer just the data you need. However, you must associate a Word field with each underlying Access field that you want to copy to the Word form. To add the first field, complete the following steps in the Word form:
- Display the Forms toolbar by choosing Toolbars from the View menu and selecting Forms.
- In the document, click the table cell beneath the Customer Number heading.
- Click the Text Form Field button on the Forms toolbar.
- Double-click the field to display the Text Form Field Options dialog box.
- Rename the field fldCustomerID and click OK.
Repeat steps 2 through 5 to add a text field for each heading, using the following list to name each field:
| Word field heading | Field name | Corresponding field in Access table |
| Customer Number | fldCustomerID | CustomerID |
| Customer | fldCompanyName | CompanyName |
| Contact | fldContactName | ContactName |
| Title | fldContactTitle | ContactTitle |
| Street Address | fldAddress | Address |
| City | fldCity | City |
| Region | fldRegion | Region |
| ZIP Code | fldPostalCode | PostalCode |
| Country | fldCountry | Country |
| Phone Number | fldPhone | Phone |
| Fax Number | fldFax | Fax |
The field heading doesn’t have to match the field names. However, notice that the Word field names match the Access field names, with an additional fld prefix. It isn’t necessary to name the Word fields similarly to their corresponding Access fields, but doing so is self-documenting and simplifies your work.
After adding all the fields, click the Protect Form button. Doing so disables a number of features, which will keep casual users from altering your form. Then, save the form as CustomerSlip.doc and close it.
Identifying the Access data
The most common method you’ll probably use to identify a specific record is an Access form. To simply the example, use the existing Customers form in Northwind. If you’d rather not alter the actual object, use a copy or use the AutoForm wizard to generate a quick form based on the Customers table. When you have a form bound to the Customers table, add a command button to the form and name it cmdPrint, as shown in Figure C.
Figure C: The Print Customer Slip command button executes the VBA code that will pass Access data to the Word form.
With the form still in Design view, click the Code button to launch the form’s module. Add the code in Listing A to the module. The cmdPrint button’s Print event does all the work of copying data for the current customer in the Customers form to the Word document (CustomerSlip.doc).
Listing A
Private Sub cmdPrint_Click()
'Print customer slip for current customer.
Dim appWord As Word.Application
Dim doc As Word.Document
'Avoid error 429, when Word isn't open.
On Error Resume Next
Err.Clear
'Set appWord object variable to running instance of Word.
Set appWord = GetObject(, "Word.Application")
If Err.Number <> 0 Then
'If Word isn't open, create a new instance of Word.
Set appWord = New Word.Application
End If
Set doc = appWord.Documents.Open("C:\WordForms\CustomerSlip.doc", , True)
With doc
.FormFields("fldCustomerID").Result = Me!CustomerID
.FormFields("fldCompanyName").Result = Me!CompanyName
.FormFields("fldContactName").Result = Me!ContactName
.FormFields("fldContactTitle").Result = Me!ContactTitle
.FormFields("fldAddress").Result = Me!Address
.FormFields("fldCity").Result = Me!City
.FormFields("fldRegion").Result = Me!Region
.FormFields("fldPostalCode").Result = Me!PostalCode
.FormFields("fldCountry").Result = Me!Country
.FormFields("fldPhone").Result = Me!Phone
.FormFields("fldFax").Result = Me!Fax
.Visible = True
.Activate
End With
Set doc = Nothing
Set appWord = Nothing
Exit Sub
errHandler:
MsgBox Err.Number & ": " & Err.Description
End Sub
How it works
First, the code creates an instance of Word. If Word is already open, it uses the currently running instance. The current code uses the expression Err.Number <> 0 because Microsoft is notorious for changing error numbers from version to version. You might want to be more specific by changing 0 to 429 (or the appropriate error number for your version if different).
Next, the Open method identifies the Word form (document). In this case, that’s CustomerSlip.doc in the WordForms folder on the C:\ drive. The optional True value opens the file as read-only. Update the path to accommodate your system. In fact, you might consider moving the filename and path to constants in the General Declarations section. That way, you can more easily update the code if you change the filename or move it.
The With block copies the data from each Access field to its corresponding Word field. After copying all the data, the Visible and Activate methods display and then select the filled in Word form, as shown in Figure D.
Figure D: The form will look like this after all the data is copied and filled.
The error handling is minimal. Be sure to thoroughly test your production solution and accommodate all possible errors. The most common errors you’ll run into are a missing Word file, an incorrect path, or mismatched field names.
Filling multiple Word forms
Currently, the example works with only one record, the form’s current record. Sometimes, you’ll want to pass multiple records. Fortunately, it isn’t difficult to enhance the existing code to handle multiple records and forms, as shown in Listing B.
Listing B
Private Sub cmdPrint_Click()
'Print customer slip for current customer.
Dim appWord As Word.Application
Dim doc As Word.Document
Dim rst As ADODB.Recordset
'Avoid error 429, when Word isn't open.
On Error Resume Next
Err.Clear
'Set appWord object variable to running instance of Word.
Set appWord = GetObject(, "Word.Application")
If Err.Number <> 0 Then
'If Word isn't open, create a new instance of Word.
Set appWord = New Word.Application
End If
'Populate recordset object.
Set rst = New ADODB.Recordset
rst.Open Me.RecordSource, CurrentProject.Connection
'Cycle through records to fill Word form fields.
Do While Not rst.EOF
Set doc = appWord.Documents.Open("C:\WordForms\CustomerSlip.doc", , True)
With doc
.FormFields("fldCustomerID").Result = rst!CustomerID
.FormFields("fldCompanyName").Result = rst!CompanyName
.FormFields("fldContactName").Result = rst!ContactName
.FormFields("fldContactTitle").Result = rst!ContactTitle
.FormFields("fldAddress").Result = rst!Address
.FormFields("fldCity").Result = rst!City
.FormFields("fldRegion").Result = rst!Region
.FormFields("fldPostalCode").Result = rst!PostalCode
.FormFields("fldCountry").Result = rst!Country
.FormFields("fldPhone").Result = rst!Phone
.FormFields("fldFax").Result = rst!Fax
.Visible = True
.Activate
'.PrintOut
'.SaveAs "'" & rst!CustomerID & "'"
rst.MoveNext
End With
Loop
Set doc = Nothing
Set appWord = Nothing
Exit Sub
errHandler:
MsgBox Err.Number & ": " & Err.Description
End Sub
Instead of retrieving data from the actual form (using the Me identifier), this procedure uses a Recordset object to pass data from multiple records. Be sure to update the .FieldForm statements by referring to the recordset (rst!) instead of the Access form (Me!), as the previous procedure did.
Our example uses the form’s RecordSource property to retrieve data from the Access form’s underlying data source (the Customers table). Consequently, the form isn’t synchronized to the procedure, so the form’s current record never changes. We’re just using the form’s recordset and print button to keep the example simple. When applying this technique to your database, you can use any valid recordset source.
Notice that the code has two commented statements toward the end of the With block. Those are actions you might possibly want to take for each Word form. The first prints the completed form; the second saves the form to the current directory using the current record’s primary key value as the form’s name. You could use either or both. If you don’t save the current record, Word dumps it when the Do While loop processes the next record.
The downside is performance. If you’re working with thousands of records, this process will take a few minutes to complete.
Shortcut to Word forms
Filling a Word form with Access data can be a useful when your data is in Access and you must use Word’s formatting features to create just the right display. Fortunately, the process is simple, whether sharing one record or thousands.
