• Back to the Mailbag
• Fill a Form Field Dropdown List on a Word Document from a Database

Make a small donation to help support this site.

Can I Have Cascading Dropdown Lists on a Word Form?

The Problem...

You have two Dropdown form fields (combo boxes) on a Microsoft Word form and you want the contents of the second Dropdown list to depend upon the selection the user made from the first one. This requirement is not unusual and is a feature often seen on web forms. Here's how it's done in a Word form...

The Solution(s)...

I'm offering two different solutions. The first has list items "hard-coded" into it. The second retrieves the list items from a database. Both methods require some VBA coding so start by adding a new code module to your document.

If you were working in a Microsoft Access form, or in a VBA UserForm, your code would run on the AfterUpdate event of the first combo box. Word form fields don't offer a set of programmable event procedures but they do allow you to specify a macro to run when the user enters or exits the form field.

Open the Visual Basic Editor and add a code module to your Word document (right-click on the document in the Project Explorer and choose Insert > Module):

Solution 1: Hard Coding the List Items...

In this example the list items are "hard-coded" into the VBA code. You might use this method if you knew in advance what those items needed to be, and that they were unlikely to change regularly (because this would mean changing the code). It is simple, robust and completely self-contained.

The example I have used here has a form which includes two Dropdown form fields (Dropdown1 and Dropdown2). The first Dropdown has a list of Regions. The second Dropdown has a list of Countries. The Countries in the list depend upon which Region was chosen.

Create a Macro to Fill the List

Items can be added to the first Dropdown in the usual way using the form field's Properties window. For filling the second Dropdown list, write a macro in the code module similar to the one below...

Sub CascadeList()
    If ActiveDocument.FormFields("DropDown1").DropDown.Value = 0 Then
        ActiveDocument.FormFields("Dropdown2").DropDown.ListEntries.Clear
        Exit Sub
     End If
     Select Case ActiveDocument.FormFields("Dropdown1").Result
        Case "North"
            With ActiveDocument.FormFields("Dropdown2").DropDown.ListEntries
                .Clear
                .Add "Iceland"
                .Add "Finland"
                .Add "Norway"
                .Add "Sweden"
            End With
        Case "South"
            With ActiveDocument.FormFields("Dropdown2").DropDown.ListEntries
                .Clear
                .Add "Greece"
                .Add "Italy"
                .Add "Portugal"
                .Add "Spain"
            End With
        Case "East"
            With ActiveDocument.FormFields("Dropdown2").DropDown.ListEntries
                .Clear
                .Add "Czechoslovakia"
                .Add "Hungary"
                .Add "Poland"
                .Add "Romania"
            End With
        Case "West"
            With ActiveDocument.FormFields("Dropdown2").DropDown.ListEntries
                .Clear
                .Add "Belgium"
                .Add "Denmark"
                .Add "France"
                .Add "Netherlands"
            End With
     End Select
 End Sub

Apply the Macro to the Dropdown

The macro needs to run when the user exits the first Dropdown form field, having made their choice. Right-click on the first Dropdown form field and open its Properties dialog. Open the list marked Run macro on... Exit to reveal a list of available macros and choose the one you created.

How it Works

The first part of the macro is an If Statement that checks whether the user has made a choice from the first Dropdown. If they have not chosen anything (i.e. its value is zero) the macro clears the second Dropdown and exits.

If an item has been chosen the macro proceeds with a Case Statement having a separate case for each item on the first Dropdown list. The code within the appropriate case is run by the macro, first clearing the second Dropdown then adding the new items to the list one at a time.

Solution 2: Getting the List Items from a Database...

An earlier Mailbag item explained how to fill a Dropdown form field list with items from a database (find it here). This solution uses a similar technique to retrieve the items for the second Dropdown form field by querying the same database using the user's selection from the first Dropdown in the query criteria.

The example I have used here has a form which includes two Dropdown form fields (Dropdown1 and Dropdown2). The first Dropdown has a list of Departments. The second Dropdown has a list of names. The names in the list depend upon which department was chosen.

Create a Macro to Fill the List

This macro is going to use ADO coding to communicate with the database so, if you haven't already done so, set a reference to ADO by going to Tools > References and choosing Microsoft ActiveX Data Objects 2.x Library (where x is the highest available number) from the list.

Write a new macro similar to the one below...

Sub CascadeList()
    On Error GoTo CascadeList_Err
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strDepartment As String
    Dim strSQL As String
    If ActiveDocument.FormFields("DropDown1").DropDown.Value = 0 Then
        ActiveDocument.FormFields("Dropdown2").DropDown.ListEntries.Clear
        Exit Sub
    End If
    strDepartment = ActiveDocument.FormFields("Dropdown1").Result
    strSQL = "SELECT TOP 25 [FullName] " & _
             "FROM tblStaff " & _
             "WHERE [Department]='" & strDepartment & "' " & _
             "ORDER BY [LastName];"
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:Databases\StaffDatabase.mdb"
    rst.Open strSQL, cnn, adOpenStatic
    rstMoveFirst
    With ActiveDocument.FormFields("Dropdown2").DropDown.ListEntries
        .Clear
        Do
            .Add rst![FullName]
            rst.MoveNext
        Loop Until rst.EOF
    End With
CascadeList_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
CascadeList_Err:
    MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
    Resume CascadeList_Exit
End Sub

Modify the Code to Suit Your Requirements

In the example the two Dropdown form fields are called Dropdown1 and Dropdown2. When you add a form field Word names it automatically. To find out what a form field is called (and rename it if you wish) right-click the form field and open its Properties dialog. The name is shown as the Bookmark property.

Write an SQL statement to retrieve the information you need from the database table. This SQL statement has several important features. In the example the SQL statement starts with SELECT TOP 25 because a Dropdown form field list can hold a maximum of only 25 items. This ensures that no more than 25 records are retrieved.

The WHERE clause of the SQL statement includes the user's selection from the first Dropdown which has been stored in the variable strDepartment.

If you are not familiar with writing your own SQL statements read some of my SQL tutorials or build a suitable query in Access and look at its SQL View to get the code.

The Connection String (in the statement beginning cnn.Open) that instructs the macro how to connect to the database specifies a Microsoft Access driver and must include the full name and path of the database. Search for help on ADO Connection Strings if you need to connect to a different type of database such as SQL Server.

How it Works

The code starts with an error handler instruction and the various variable declarations (it is always a good idea to include an error handler in your code when dealing with an external item such as a database).

An If Statement checks the first Dropdown. If the user has not made a selection from the list (i.e. the Dropdown remains empty and its value is zero) the database code will not work. To prevent the macro crashing there is an instruction to exit here if that is the case. Before exiting the macro also clears the list items from the second Dropdown.

Assuming that the user did make a selection, that item (the form field's Result) is stored in a variable (strDepartment) for later inclusion in the SQL statement. Next an SQL statement is built, using the strDepartment variable value in its WHERE clause. The SQL statement is also stored in a variable (strSQL).

A connection is made to the database and a recordset based on the SQL statement is opened in the computer's memory. The code then loops through the recordset adding an item to the Dropdown's list for each record it visits. Finally the Exit routine closes the recordset and connection and clears their object variables.

Apply the Macro to the Dropdown

The macro needs to run when the user exits the first Dropdown form field, having made their choice. Right-click on the first Dropdown form field and open its Properties dialog. Open the list marked Run macro on... Exit to reveal a list of available macros and choose the one you created (see the illustration for Solution 1) .

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