How Do I Fill a Form Field Dropdown List in a Word Document with
Items from a Database?
When creating a form in a Word document you can
add a Dropdown List (we propellorheads call them Combo Boxes)
offering the user a selection of items to choose from. The normal
procedure is to "hard code" the list by entering the list items manually
into the properties window of the form field...
That's OK if the list is not going to change. But if your list
changes from time to time you need to keep it up-to-date. So, if the
list is made up from information in a database, is it possible to
have the list update automatically?
The answer is Yes, thanks to ADO and a little VBA
programming. ADO (ActiveX Data Objects) is part of the VBA
programming language which allows any Microsoft Office application
to talk to a database. Instead of manually defining the items on the
form field's list you can use ADO to retrieve the list from a
database. This example uses Microsoft Access but ADO can talk to
almost any kind of database.
Get the Form Field's Name
You need to know what the dropdown form field is called. Word
automatically assigns a name to each form field as it is added to
the document, or you might have named it yourself. Right-click the
form field and open its Properties dialog. The name is shown as the
Bookmark property (e.g. "Dropdown1").
Set a Reference to
So that Word understands your ADO coding you need to set a
reference to the ADO object library. Open the Visual Basic Editor
(keys: [Alt]+[F11]) then choose Tools > References to
open the References dialog. Scroll down the list until you find an
entry for the Microsoft ActiveX Data Objects 2.x Library
(where x is the highest available number) then place a tick
in the adjacent checkbox and click the OK button.
The code should run each time the document is opened so that the
list is always up-to-date. If you plan to keep your form as a Word
Document (*.doc) file then the code should run on the document's
Open event. But if you plan to save your document as a Word
Template (*.dot) file then the code should run on the document's
New event (i.e. when a new document based on the template is
Both of these events belong in the document's
ThisDocument module. In the Visual Basic Editor double click the
ThisDocument entry in the Project Explorer to open the code
The code listing below runs on the Document_Open event,
suitable for a Word document. If yours is a Word template change the
name of the procedure to Document_New. The code contains a
number of statements that you will have to edit to suit your
The Connection String (the line starting
cnn.Open) specifies a provider suitable for a Microsoft
Access database, and also includes the database filename and
path. Search for help on ADO Connection Strings if you
need to connect to a different type of database such as SQL
The rst.Open statement is a simple SQL
statement that generates a list of unique items from a named
field of a named table and, in this example, also defines the
sort order. If you don't know SQL read some of my SQL tutorials
or build a suitable query in Access and look at its SQL View to
get the code.
The form field to which the list items are added
is referred to by name (here it is Dropdown1).
The loop refers again to the name of the field
(here [Department]) from which the list items are taken.
Note that I have also included error handling in the
On Error GoTo Document_Open_Err
Dim cnn As
Dim rst As
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
rst.Open "SELECT DISTINCT TOP 25 [Department] FROM tblStaff ORDER BY
Loop Until rst.EOF
On Error Resume Next
Set rst =
Set cnn =
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical,
How it Works
The code starts by connecting to the named database and opening a
recordset in the computer's memory. The recordset is defined by the
SQL statement which creates a list of unique items (here it is
Department names) from a named table (here called tblStaff).
Note that the SQL statement includes the instruction TOP 25
to ensure that it returns no more than 25 items. A form field
Dropdown can have a maximum of only 25 items on its list.
After moving to the first record in the recordset the procedure
first clears any existing items from the form field list. Then a
loop moves through the recordset, adding an item to the form field
list for each record it visits.
Finally, the exit procedure
ensures that the recordset and database connection are closed and
their object variables set to Nothing to clear the memory
before it exits.