Word Code

Memo with Database Information

This is quite a common requirement; to have a template which takes some information from a database. In this example we have a Memo template (though it could be anything) which has details of the sender in a database. The information in this example is just confined to the sender's full name, phone number and fax number.

Of course the information held could be almost anything, but this example is enough to get the developer up and running.

The code makes the assumption that the database, FeeEarners.mdb, is held in the Workgroups folder. If this isn't applicable for you feel free to change the location of the database which is in the OpenDatabase routine.

The database is accessed via ADO. The code assumes that the version of ADO is 2.6. If you are running a different version then all you will have to do is to change the reference to the correct version within Tools | References of the IDE.

When a new document is created on this template the following dialog box opens. Note that the complete list of the senders' initials are listed in the list box.

The initial dialog box

When the user selects an entry from the list and then presses the OK button the details are taken from the database and inserted into the document in the correct place, as can be seen in the next picture.

The information inserted into the document (partial screenshot)

Don't bother ringing that number; it's not mine.

The code is in two places; the ThisDocument class and the form itself, which is clsForm.

The code for the class ThisDocument:

Option Explicit

Private Sub Document_New()

  Dim oForm As clsForm
  
  On Error Resume Next
  
  
  Set oForm = New clsForm
  oForm.txtDate = Format$(Date, "d MMMM yyyy")
  oForm.Tag = "Cancel"
  oForm.Show
  ' --------------------
  If oForm.Tag = "Cancel" Then
    ActiveDocument.Close wdDoNotSaveChanges
  Else
    ActiveDocument.Bookmarks("bmkStartHere").Range.Select
  End If
  
  Unload oForm
  Set oForm = Nothing

End Sub

 

Nothing unusual there, just the standard way of creating a form object from the class and then shows it. Note that if the user clicks on the Cancel button on the form then the document will close.

The code for the form clsForm:

Option Explicit

  Dim oConnection As ADODB.Connection
  Dim sConnection As String
  Dim sDatabasePath As String
'


Private Sub UserForm_Initialize()

  ' Populate the list box with fee earners' initials
  
  Dim oRS As ADODB.Recordset
  
  
  Dim sInitials As String
  
  OpenDatabase
  
  Set oRS = New ADODB.Recordset
  oRS.Open "FeeEarners", oConnection, adOpenForwardOnly, adLockReadOnly, adCmdTable

  Do While Not oRS.EOF
    sInitials = "" & oRS("Initials")
    Me.lstFeeEarners.AddItem sInitials
    oRS.MoveNext
  Loop
  oRS.Close
  Set oRS = Nothing
  oConnection.Close
  Set oConnection = Nothing

End Sub


Private Sub cmdCancel_Click()

  Me.Tag = "Cancel"
  Me.Hide

End Sub


Private Sub cmdOK_Click()

  Dim oRS As ADODB.Recordset
  
  Dim SQL As String
  Dim sInitials As String
  Dim sName As String
  Dim sPhone As String
  Dim sFax As String
  
  
  If Me.lstFeeEarners.ListIndex > -1 Then
    sInitials = Me.lstFeeEarners.Value
    SQL = "SELECT FeeEarners.* FROM FeeEarners WHERE (((FeeEarners.Initials)=""" & sInitials & """));"
  
    OpenDatabase
  
    Set oRS = New ADODB.Recordset
    oRS.Open SQL, oConnection, adOpenForwardOnly, adLockReadOnly, adCmdText

    If Not oRS.EOF Then
      sName = "" & oRS("Name")
      sPhone = "" & oRS("Phone")
      sFax = "" & oRS("Fax")
    End If
    oRS.Close
    Set oRS = Nothing
    oConnection.Close
    Set oConnection = Nothing
    
    ActiveDocument.Bookmarks("bmkFrom").Range.Text = sName
    ActiveDocument.Bookmarks("bmkPhone").Range.Text = sPhone
    ActiveDocument.Bookmarks("bmkFax").Range.Text = sFax
    
    ActiveDocument.Bookmarks("bmkTo").Range.Text = Me.txtTo.Text
    ActiveDocument.Bookmarks("bmkDate").Range.Text = Me.txtDate.Text
    ActiveDocument.Bookmarks("bmkTitle").Range.Text = Me.txtTitle.Text
    
    Me.Tag = "OK"
    Me.Hide
  End If
  
End Sub



Private Sub OpenDatabase()
  
  sDatabasePath = Options.DefaultFilePath(wdWorkgroupTemplatesPath) & "\FeeEarners.mdb"
  sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDatabasePath

  Set oConnection = New ADODB.Connection
  oConnection.ConnectionTimeout = 0
  oConnection.ConnectionString = sConnection
  oConnection.Open
  
End Sub

Assuming that the developer has installed ADO and compiles then it ought to run as in the example.

When the form is initialised the Form_Initialize() event handler runs. This calls the routine OpenDatabase() which creates the database connection. It is in here that the location of the database is referenced. In this example the database is called FeeEarners.mdb and is located in Word's Workgroup folder. If this location is not appropriate then please feel free to amend it.

Once the database is opened a simple RecordSet is opened and then rattles through the Initials field and then adds the contents of the field into the list box.

After that is done the Recordset is closed as is the Connection and the references cleaned up.

Given that the user clicks on the OK button after selecting an initial then other database connection is opened and then a SQL statement is contructed to locate the record whose initials match the initials chosen.

If this record exists (and it should be given the the initials came from the same table) then the values from the required fields are placed into the bookmark ranges on the document. In addition the information from the form's text boxes are also placed into the form.

Like before, the database RecordSet and Connections are closed and dereferenced.

And there it is; a very simple example of using an ADO connection to get data via VBA. From the basic principles shown here one is able to do almost anything with datbases.

This code can be downloaded from here, unzipped and placed into Word's Workgroup folder.

Updates or Comments

If there are any suggestions for updates or comments then please drop us a mail at malcolm.smith@dragondrop.com.