Visual Basic User's Guide > Mail Merge
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.