# Merging Database Data and Text

> Fill the Marked Text Fields of a template with a database record in Visual Basic 6: find fields by FieldData name with FieldNext, set FieldText with DAO.

- **Product:** TX Text Control ActiveX
- **Section:** Documentation
- **Kind:** Topic
- **Path:** TX Text Control ActiveX > Mail Merge > Merging Database Data and Text
- **1484 words**
- **Web URL:** https://docs.textcontrol.com/tx-text-control-activex/mail-merge/merging-data/
- **LLMs URL:** https://docs.textcontrol.com/tx-text-control-activex/mail-merge/merging-data/llms.txt
- **LLMs-Full URL:** https://docs.textcontrol.com/tx-text-control-activex/mail-merge/merging-data/llms-full.txt

---

A mail merge template is a document with Marked Text Fields at the positions where database data belongs. The first part of the Visual Basic mail merge sample loads such a template into a `TXTextControl`, browses the records of a database and copies the fields of the current record into the matching fields of the document.

The sample consists of two forms: *Form1* creates the text template, and *Form2* connects it to the database. The complete project is in the *Samples\\vb6\\Mailmerge\\Step1* sample directory. The code below concentrates on the parts in which the functionality of TX Text Control is used.

### Run the Sample

1. Start the program and choose **Template / Load...** to load a sample template file, for example *template1.tx*. The file contains several fields that are replaced by database entries.
2. Choose **Database / Browse Database** to open *Form2*. You can now move through the database records connected with the template.
3. Click **Merge** in *Form2*. The contents of the three database fields are copied to the text fields in *Form1*.
4. Select a different record with one of the data control buttons in *Form2* and click **Merge** again to replace the field contents.

![The template loaded in Form1 of the mail merge sample, with the fields that database entries replace.](https://docs.textcontrol.com/assets/img/tx-text-control-activex/mail-merge-template.8a6e2f2a.webp)

![The Browse Database form of the mail merge sample, with the data control buttons and the Merge button.](https://docs.textcontrol.com/assets/img/tx-text-control-activex/browse-database-form.6746c85e.webp)

### How the Template Identifies Its Fields

The template file that the menu item loads already contains several Marked Text Fields. To receive data from a database, each of them has a unique name, and this name is saved in the [FieldData](https://docs.textcontrol.com/tx-text-control-activex/reference/txtextcontrol/fielddata-property/llms-full.txt) property of the field. The field names of the template match the field names of the database.

A second menu item opens a further form, which links a suitably prepared database. A button click merges the current record into the document.

### Find a Field by Its Name

The most important function of the sample merges a data field of the linked database into a field of the `TXTextControl`. For this, it needs the field number of the Marked Text Field that belongs to the data field. The following function returns it:

```vbscript
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
```

The code works as follows:

- [FieldNext](https://docs.textcontrol.com/tx-text-control-activex/reference/txtextcontrol/fieldnext-method/llms-full.txt) steps through all fields of the document in a loop, starting with `0` and ending when it returns `0`.
- For each field, the function compares its [FieldData](https://docs.textcontrol.com/tx-text-control-activex/reference/txtextcontrol/fielddata-property/llms-full.txt) property with the name it searches for.
- If a field matches, the function returns its ID. If no field is found, it returns `0`.

### Merge a Record Into the Fields

A second function merges the field data with the database. It opens the database and fills each Marked Text Field of the template with the data of the database field of the same name:

```vbscript
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
```

The code works as follows:

- The function opens the database of the data control on *Form2* and selects the record whose second field matches the `field` parameter.
- For each field of the record, `FindField` returns the ID of the Marked Text Field with the same name.
- [FieldCurrent](https://docs.textcontrol.com/tx-text-control-activex/reference/txtextcontrol/fieldcurrent-property/llms-full.txt) makes that field the current field, and [FieldText](https://docs.textcontrol.com/tx-text-control-activex/reference/txtextcontrol/fieldtext-property/llms-full.txt) replaces its text with the data from the database.

### Next Steps

- [Printing More Than One Record](https://docs.textcontrol.com/tx-text-control-activex/mail-merge/multiple-records/llms-full.txt): add records to a print queue and print them with a hidden `TXTextControl`.
- [Relating Data to a Marked Text Field](https://docs.textcontrol.com/tx-text-control-activex/document-editor/fields/data/llms-full.txt): how the `FieldData` property stores data with a field.
- [Mail Merge](https://docs.textcontrol.com/tx-text-control-activex/mail-merge/llms-full.txt): the parts of the sample and where to start.

---

## Nearby

- **Previous:** [Mail Merge](https://docs.textcontrol.com/tx-text-control-activex/mail-merge/llms-full.txt)
- **Next:** [Printing More Than One Record](https://docs.textcontrol.com/tx-text-control-activex/mail-merge/multiple-records/llms-full.txt)