Step 1 - Merging Database Data and Text

The program consists of two forms, Form1, for creating a text template and Form2 for connecting it to the database.

Start the program and use the Template/ Load... command to load the sample template file e.g. template1.tx. The file contains various fields which are to be replaced by database entries. Select Database / Browse Database to access Form2. Now you are able to skip through the database entries connected with the template. When you click the Merge button in Form2, the contents of the three database fields are copied to the text fields in Form1. You can select a different record by clicking one of the data control buttons in Form2, and then clicking Merge again to replace the fields.

This example has a couple of interesting features. We will focus on the features in which Text Control's functionality is primaraily used. You will find a menu item, with which a template file can be loaded. This file already contains several Marked Text Fields. So we can get some data from a database into these fields, each one has a unique name. These unique names are saved in the FieldData property.

Using a second menu item, a further form can be opened - this allows a suitably prepared database to be linked. With a button click, the current data set can be merged into the document.

The most important function of this example is the one that allows a data field from the linked database to be merged into a field from Text Control. To get this to work, we need to get the field number of the Marked Text Field that belongs to the data field. This can be achieved using the following code:

[Visual Basic 6]
Function FindField(name As String) As Integer
   Dim id As Integer
   Dim foundID As Integer
   foundID = 0
   id = Form1.TXTextControl1.FieldNext(0, 0)
   While id <> 0
      If Form1.TXTextControl1.FieldData(id) = name Then
         foundID = id
      End If
      id = Form1.TXTextControl1.FieldNext(id, 0)
   Wend
   If foundID <> 0 Then
      FindField = foundID
   Else
      FindField = 0
   End If
End Function

Using a loop we go through all the fields from the Text Control - and the FieldData property belonging to each field - to check whether it is the same as the name we are searching for. If we get a hit, its' ID is returned. If no field is found, then a zero is returned.

For the merging of the field data with the database we will have to add another function to our application. Let's have a look at the source code:

[Visual Basic 6]
Sub Merge(TXDest As TXTextControl, field As String)
   Dim id As Integer
   Dim i As Integer
   Dim db As Database
   Dim rs As Recordset
   Dim data, SQL As String
   ' Open database
   Set db = Form2.Data1.Database
   Set rs = db.OpenRecordset(db.TableDefs(0).name)
   ' Find record
   SQL = "SELECT * FROM " + db.TableDefs(0).name + _
        " where " + rs.Fields(1).name + " like '" +_
        field + "'"
   Set rs = db.OpenRecordset(SQL)
   rs.MoveFirst
   ' Now fill all fields with data
   For i = 0 To rs.Fields.Count - 1
      ' Get ID of field with the according name
      id = FindField(rs.Fields(i).name)
      ' Get data from database
      data = rs(rs.Fields(i).name)
      If id <> 0 Then
         TXDest.FieldCurrent = id
         TXDest.FieldText = data
      End If
   Next i
End Sub

This function opens the database, and fills up each Marked Text Field of the template with the data from each database field. You will find the whole sample project in the Samples\vb6\Mailmerge\Step1 sample directory.