How do I... Dynamically fill Microsoft Word fields using Access data?

This blog post is also available in PDF form as a TechRepublic download. The download includes a sample Access database and Word form.

Each Microsoft Office application specializes in a specific job. Word lets you create and edit documents and Excel analyzes your data. Storing data is Access' claim to fame. Fortunately, Office applications share data easily. For instance, you might use Word's mail merge feature to complete invoices in a Word form using customer data from an Access database.

Sometimes the task is more complicated. Perhaps you want to filter Access data based on the contents of a Word field. That takes a bit more work than a simple mail merge, but it's certainly doable. The example technique in this TechRepublic How do I... populates a Word 2003 dropdown field with Access data. Then, the same form uses the selected value from that field to retrieve more Access data.

This blog post was inspired by a message from TechRepublic member njordan@... who asked: "Hello! I am desperately trying to figure out how to select a choice in a field in the beginning of my form and pre-fill the default values for other fields in the form based on that first dropdown choice."

Building the Word form

To dynamically fill in Word fields from an Access table based on the contents of another Word field, you'll need a Word form, an Access table with data, and some Visual Basic for Applications (VBA) code. A form is a Word document that contains fill-in blanks or fields, in which you enter information. A field is a predefined cell for entering and storing information. The example form in Figure A lets you choose a last name from the dropdown field and then automatically fills in the remaining text box fields with corresponding data from an Access table. Specifically, the form retrieves data from the Employees table in the Northwind database (the sample database that comes with Access).

Figure A

Use the dropdown field to dynamically fill other fields.
To create the form, start with a blank document. Display the Forms toolbar by right-clicking any toolbar and checking Forms. To add a field, position the cursor and then click the appropriate tool on the Forms toolbar. Complete this process three times to add two text box fields and one dropdown field to the form. Name them as shown in Table A by double-clicking each to open its options dialog.

Table A


Text Box

Text Box


These names correspond to field names in the Employees table (in Access). You don't have to give the Word fields similar names, but doing so simplifies your work because the names are self-documenting. To keep things simple, the example form pulls over only a few values. Retrieve only the values you need when applying this to your own work.

There's one limitation -- the dropdown field can display only 25 items. Fortunately, there are only nine employees, so Word's native field is adequate.

Adding the VBA code

To populate the dropdown field, open the Visual Basic Editor (VBE) by pressing Alt+F11. In the Project Explorer, double-click ThisDocument for the current project to launch the form's module (if necessary). Enter the sub procedure (macro) in Listing A. This procedure creates a connection to Northwind, uses SQL to create a Recordset object, and then fills that recordset with last names from the Employees table. To complete the job, the code cycles through the recordset to copy the retrieved names to wfLastName.

Listing A

Private Sub Document_Open()

'Populate employee dropdown field.

Dim db As DAO.Database

Dim rst As DAO.Recordset

Dim strSQL As String

Dim strPath As String

Dim doc As Document

Set doc = ThisDocument

strSQL = "SELECT LastName FROM Employees ORDER BY LastName"

strPath = "C:Program FilesMicrosoft " _

& "Office11OFFICE11SAMPLESNorthwind.mdb"

'Update path to database file.

Set db = OpenDatabase(strPath)

Set rst = db.OpenRecordset(strSQL)

Do While Not rst.EOF

With doc.FormFields("wfLastName").DropDown.ListEntries

.Add Name:=rst(0)

End With



Set db = Nothing

Set rst = Nothing

End Sub
This sub procedure uses the Data Access Objects (DAO) library, which is no longer Office's default library, but this is one of those times when DAO is more efficient that ActiveX Data Objects (ADO). Add DAO to the project by choosing References from the Tools menu and then checking Microsoft DAO 3.x Object Library, as shown in Figure B, and clicking OK. Don't skip this step or the form won't work.

Figure B

Add the DAO library to the project.
Next, enter the sub procedures in Listing B and Listing C to the form's module. FillDependentFields() uses the selected value in wfLastName to fill in wfTitleOfCourtesy and wfFirstName. The form's Close event clears all three fields before closing without saving your choices. In this case, you don't want to save anything, and in fact, not saving your changes protects the form's integrity.

Listing B

Sub FillDependentFields()

'Fill form fields based on selected employee

'in wfEmployeeDropdown.

Dim db As DAO.Database

Dim rst As DAO.Recordset

Dim doc As Document

Dim strSQL As String

Dim strPath As String

Set doc = ThisDocument

strSQL = "SELECT TitleOfCourtesy, FirstName FROM Employees " _

& "WHERE LastName = '" _

& doc.FormFields("wfLastName").Result _

& "'"

strPath = "C:Program FilesMicrosoft Office11OFFICE11SAMPLESNorthwind.mdb"

Set db = OpenDatabase(strPath)

Set rst = db.OpenRecordset(strSQL)

'Ignore Null values from Access data.

On Error Resume Next

doc.FormFields("wfTitleOfCourtesy").Result = rst(0).Value

doc.FormFields("wfFirstName").Result = rst(1).Value

Set db = Nothing

Set rst = Nothing

End Sub

Listing C

Private Sub Document_Close()

'Clear fields.

Dim doc As Document

Set doc = ThisDocument




'Close without saving or prompting.

ActiveDocument.Saved = True

End Sub Set rst = Nothing
Return to the form, double-click wfLastName, choose FillDependentFields from the Exit control, as shown in Figure C, and click OK. Now, exiting the dropdown field will execute the macro that fills in the two text box fields.

Figure C

Exiting the dropdown field executes the FillDependentFields() sub procedure.

You're just about done. Click the Protect Form tool (the lock) on the Forms toolbar, save the document, and close it. (The example form is named ExampleForm.doc and is included in the download version of this blog post.)

Using the dynamic form

Open the form and enable macros if prompted. Inside the document, click wfLastName's dropdown arrow to display the list of last names shown in Figure D. Choose any name from the list and press Tab to fill in wfTitleOfCourtesy and wfFirstName, as shown in Figure E.

Figure D

Click the dropdown field's arrow to see a list of last names.

Figure E

Exit the dropdown field to fill-in the text fields.

You can use the form as many times as needed. Just remember to press Tab after selecting a last name from wfLastName to update the other fields, accordingly.

Fill in the blanks, dynamically

With the help of a little VBA code, you can populate Word fields using foreign data. Not only that, you can use existing field values to filter foreign data. The result is a dynamic form that updates fields based on a selected value.