[dba-Tech] Word code to dynamically fill Word fields using Access data

The following code fills a Word dropdown field using the LastName fields
from the Northwind Employees table. Then, depending on the selected value,
it fills in three Word text fields. 

It works, but I don't know enough about Word to know if this is the best and
most efficient way to tackle this. I'm writing about this because a reader
requested a solution - so I want to make sure I'm using the best "Word"
solution -- because I've tackled this like an Access developer. 

I really expected an easier way to query Access data from Word, but didn't
find one -- but that doesn't mean there isn't one, just means I didn't find
it. 

Susan H. 



Option Explicit

Sub FillDependentFields()
  'Fill form fields based on selected employee
  'in wfEmployeeDropdown.
  Dim db As DAO.Database
  Dim rst As DAO.Recordset
  Dim strSQL As String
  strSQL = "SELECT FirstName, Title, BirthDate FROM Employees " _
   & "WHERE LastName = '" _
   & ThisDocument.FormFields("wfEmployeeDropdown").Result _
   & "'"
  Debug.Print strSQL
  Set db = OpenDatabase(Name:="C:\Program Files\Microsoft
Office11\OFFICE11\SAMPLES\Northwind.mdb")
  Set rst = db.OpenRecordset(strSQL)
  'Ignore Null values from Access data.
  On Error Resume Next
  ThisDocument.FormFields("wfFirstName").Result = rst(0).Value
  ThisDocument.FormFields("wfTitle").Result = rst(1).Value
  ThisDocument.FormFields("wfBirthDate").Result = rst(2).Value
  Set db = Nothing
  Set rst = Nothing
End Sub

Private Sub Document_Close()
  'Clear form fields.
  ActiveDocument.FormFields("wfEmployeeDropdown").DropDown.ListEntries.Clear
  ActiveDocument.FormFields("wfFirstName").TextInput.Clear
  ActiveDocument.FormFields("wfTitle").TextInput.Clear
  ActiveDocument.FormFields("wfBirthDate").TextInput.Clear
End Sub

Private Sub Document_Open()
  'Populate employee dropdown field.
  Dim db As DAO.Database
  Dim rst As DAO.Recordset
  Dim strSQL As String
  strSQL = "SELECT LastName FROM Employees ORDER BY LastName"
  Debug.Print strSQL
  'Update path to database file.
  Set db = OpenDatabase(Name:="C:\Program Files\Microsoft
Office11\OFFICE11\SAMPLES\Northwind.mdb")
  Set rst = db.OpenRecordset(strSQL)
  Do While Not rst.EOF
    With
ActiveDocument.FormFields("wfEmployeeDropdown").DropDown.ListEntries
      .Add Name:=rst(0)
    End With
    rst.MoveNext
  Loop
  Set db = Nothing
  Set rst = Nothing
End Sub