• Back to the Mailbag
• Use the Same technique to Fill a Combo Box or List Box on a VBA UserForm
• Create Cascading Dropdowns on a Word Form

Make a small donation to help support this site.

How Do I Fill a Form Field Dropdown List in a Word Document with Items from a Database?

The Problem...

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

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 ADO

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.

Write the Code

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 created).

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

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 particular database:

• 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 Server.

• 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 code...

Private Sub Document_Open()
    On Error GoTo Document_Open_Err
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Databases\StaffDatabase.mdb"
    rst.Open "SELECT DISTINCT TOP 25 [Department] FROM tblStaff ORDER BY [Department];", _
        cnn, adOpenStatic
    rst.MoveFirst
    With ActiveDocument.FormFields("Dropdown1").DropDown.ListEntries
        .Clear
        Do
            .Add rst![Department]
            rst.MoveNext
        Loop Until rst.EOF
    End With
Document_Open_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
Document_Open_Err:
    MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
    Resume Document_Open_Exit
End Sub

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.

©2007 Martin Green. All rights reserved. martin@fontstuff.com