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.
If there are any suggestions for updates or comments then please drop us a mail at firstname.lastname@example.org.