Software

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

wfTitleOfCourtesy

Text Box

wfFirstName

Text Box

wfLastName

Dropdown

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

rst.MoveNext

Loop

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

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

doc.FormFields("wfFirstName").TextInput.Clear

doc.FormFields("wfTitleOfCourtesy").TextInput.Clear

'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.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

26 comments
sbeaudreau
sbeaudreau

Hi, I really need help with this. This is such a cool function and I really want it to work. I downloaded the example and it had issues for me right out of the box. It looks like there may be some type of issue with the loop since my drop down is populating last names multiple times, I get the error about "the drop down cannot have more than 25 items". I have double-checked the northwind employee table and there are only 9 records in that table. If someone could help I would really appreciate it. I am using Office 2003.

ssharkins
ssharkins

Dear Lyn_Hall You can send your template to ssharkins at gmail dot com. I won't promise to fix the problem, but I'll take a look.

fox_iacmnf
fox_iacmnf

Hello! I'm trying to implement this in a project and I don't have the database's path, just ODBC connection info that I got from a {Database} field: "DSN=MARIOS;Description=MARIOS;UID=mmj;PWD=**;APP=Microsoft?? Query;WSID=me;DATABASE=Jupiter;UseProcForPrepare=0" How can I do what you describe above in this case? Thank you in advance for any help!

lyn_hall
lyn_hall

I am totally confused. Here is what I need to do: Last name, first name, MI DOB pre-fill when the correct last name is chosen from dropdown menu in word 2007. I can't figure out the coding... Please help...

pathfinder8008
pathfinder8008

In my copy of MS Word 2003 I do not get the FillDependentFields option under exit or entry. I can select it in your example .doc but cannot reproduce this action in my own from scratch. As I was enjoying this I would like to continue. Any light on this?

jbleahy
jbleahy

Any directions to do this with W 2007?

tech
tech

not so good

d
d

Your "printer" messed up your code by removing backslashes. strPath = "C:Program FilesMicrosoft " _ & "Office11OFFICE11SAMPLESNorthwind.mdb" should read strPath = "C:\Program Files\Microsoft " _ & "Office11\OFFICE11\SAMPLES\Northwind.mdb" For other versions of Access, Northwind.mdb will be located elsewhere or may not exist on your hard disk at all. At a glance, the code seems to be solid and well-written. Thanks!

cfbandit
cfbandit

That's all well and good - but how do you translate this process into Word 2007?

thisisfutile
thisisfutile

Good topic. I've recently helped our church set up a couple of small computers in their office and they purchased Office 2003 to install on them. They wanted help setting up something like this and frankly, even though I'm an IT Manager and work extensively in SQL Server and our front end application, I've only glanced at Word and Access (as front and back end data management). This is going to be my springboard for helping them. Thank you for this post.

ssharkins
ssharkins

Follow the example article, but use the Access field names and other appropriate references instead of the example's. You don't say where you're getting lost, but if you'll tell us what's going wrong, I can try to help.

ssharkins
ssharkins

I'm really glad so many of you have found it useful.

ssharkins
ssharkins

Did you enter FillDependentFields as a sub procedure? If you created a function procedure, Word doesn't consider it a "macro." That's my first guess -- if that's not it, get back to me and we'll work through it.

ssharkins
ssharkins

I have no idea what happened -- I'll try to make sure it doesn't happen again. Thanks for pointing that out.

tv_p
tv_p

Thanks for the helpful post. How can I count the number of typed characters, spaces, tabs, keyboard returns etc in completed word document? Was there any solution or technique to accomplish in programmatically? I do not want to use built in Word count menu item.

kchambers
kchambers

Hello, First of all, I'm not much on VB or any script for that fact. We are creating forms for out inspectors and engineers to use when working in the shops. Up till now they have simply typed the information they need, now we want to populate based on a unique job number. I can get the document to work when pulling from a single table, but not when pulling from a query (that gathers from 4 tables). On the other hand and I can make it work from a query in mail merge, but mail merge does not allow you to protect the document (very important we protect these docs). The problem I run into when I try to pull down the data from the query is that you cannot have a " . " in the bookmark name, and a " . " is always in the name of a field from a query, IE "table1.item " "table2.item" etc... Any ideas?????

ssharkins
ssharkins

Let us know how the project goes!

lyn_hall
lyn_hall

Private Sub Document_Open() 'Populate patient 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 LNAME FROM Patient Info ORDER BY LNAME" strPath = "D:\VP Patients\Patient Info.accdb" 'Update path to database file. Set db = OpenDatabase(strPath) Set rst = db.OpenRecordset(strSQL) Do While Not rst.EOF With doc.FormFields("LNAME").DropDown.ListEntries .Add Name:=rst(0) End With rst.MoveNext Loop Set db = Nothing Set rst = Nothing End Sub Sub FillDependentFields() 'Fill form fields based on selected patient. 'in wfPatientDropdown. Dim db As DAO.Database Dim rst As DAO.Recordset Dim doc As Document Dim strSQL As String Dim strPath As String Set doc = ThisStatement strSQL = "SELECT FNAME, MI, FROM Patient Info" _ & "WHERE LNAME = '" _ & doc.FormFields("LNAME").Result _ & "'" strPath = "D:\VP Patients\Patient Info.accdb" Set db = OpenDatabase(strPath) Set rst = db.OpenRecordset(strSQL) 'Ignore Null values from Access data. On Error Resume Next doc.FormFields("FNAME").Result = rst(0).Value doc.FormFields("MI").Result = rst(1).Value Set db = Nothing Set rst = Nothing End Sub Private Sub Document_Close() 'Clear fields. Dim doc As Document Set doc = ThisDocument doc.FormFields("LNAME").DropDown.ListEntries.Clear doc.FormFields("FNAME").TextInput.Clear doc.FormFields("MI").TextInput.Clear 'Close without saving or prompting. ActiveDocument.Saved = True Set rst = Nothing End Sub Now My fields in Access are named FNAME, LNAME, MI and DOB and my file name (database file) is Patient Info.accdb I cannot get the drop-down menu to show up. In your example, under the 'Drop-Down Form Field Options' it lists items in the drop-down list, mine does not. Thanks!

abasu
abasu

Double clicking the form fields opens options dialog, but I cannot find any field for entering the name of the field. There is a field "Bookmark". Is that for entering the name?

lyn_hall
lyn_hall

The directions were about the same as yours, however the problem was the protection. That is a little different in 2007 than in 2003. On another note, is there an email that I could send you a copy of my template that I am trying to automate?

ssharkins
ssharkins

First, great that you have it working in 2007. Would you mind sharing your updates/changes? Second, I'm sorry, I don't understand what you're trying to do. All of the Word fields in the example access the same Access database, but display a specific (individual) field. If you're trying to do something different, could you give me an example?

lyn_hall
lyn_hall

Ok, so I have finally figured out how to do this in Word 2007 and it works great for one dropdown menu and 3 text fields. Now I need to have 3 sets of these on one page: Here is what I am taking about: , , , Now all three dropdown menus need to access the same database in MSAccess but allow me to choose them separately. Does this make sense to you? I need to know how to set this up in VB Editor. How do you set up three codes that have basically the same result but allow me to choose the dropdowns individually filling in the correct text fields?? I have an access 2007 for dummies book on its way, but this has now become an obsession, because it is bugging me now.

ssharkins
ssharkins

I'm sorry, but I don't think it works in Office 2007. The title specifically identifies this as a 2003 technique for this reason, but I apologize for your trouble. It really is a nuisance that things change so much from one version to another! If I can figure out how to make it work in 2007, I'll post 2007 specific instructions.

ssharkins
ssharkins

Simply replace the default bookmark setting with a descriptive name for the control. The truth is, you don't have to rename the controls, but I find that working with descriptive names makes writing the code easier.

Editor's Picks