Step 3 - Dynamic Template Generation

Visual Basic User's Guide > Mail Merge

This step will show you how to build a real mail-merge layout editor. In other words, the user should be able to select a database and insert its fields into his active document just by clicking the mouse. Then, when we start the actual mail merging process, these fields will be replaced by the data from the database.

However, the problem is that we do not know how many fields there are in the database. In other words our application must be able to work with databases of varying size.

Secondly, we are going to set up a menu item, which will call a Common Dialog so that the user can select a database. However, the database is not really loaded - just the name of the database is stored in the curDBName variable. Storing an entire database in the memory could be somewhat problematic, should the database be several terabytes in size.

Thirdly, we need to let the user insert fields from the database into this document. However, only fields which are in the database may be inserted into the active document. To do this, we use a combo box. On loading the database we then proceed to fill this combo box with the names of its fields. Using a simple button, we can then insert a field from the database into our active document.

Each piece of static code dealing with database fields has to be replaced with dynamic field names.

Consider the Browse Database form: In the previous example, the labels which display the data from the database were statically linked to the database.

Using a control array, these labels now need to be dynamically created when the form is loaded. Once they have been loaded, they then have to be linked to the corresponding database field.

To do this, we need to use the following Visual Basic code:

[Visual Basic 6]
Private Sub Form_Load()
   Data1.DatabaseName = curDBName
   doPrint = False
   TXHidden.Left = 10000 ' Move TX out of the visible area
   Dim db As Database
   Dim rs As Recordset
   ' Find record
   Set db = OpenDatabase(curDBName)
   Set rs = db.OpenRecordset(db.TableDefs(0).name)
   lblField(0).Caption = rs.Fields(0).name
   lblData(0).DataField = rs.Fields(0).name
   ' Create labels
   For i = 1 To rs.Fields.Count - 1
      Load lblField(i)
      lblField(i).Top = 320 + (i * 300)
      lblField(i).Caption = rs.Fields(i).name
      lblField(i).Visible = True
      Load lblData(i)
      lblData(i).Top = 320 + (i * 300)
      lblData(i).DataField = rs.Fields(i).name
      lblData(i).Visible = True
      ' if i > 6 We need more space
      If i > 6 Then
         Form2.Height = Form2.Height + 300
         Frame1.Height = Frame1.Height + 300
         Data1.Top = Data1.Top + 300
         cmdMerge.Top = cmdMerge.Top + 300
         cmdCancel.Top = cmdCancel.Top + 300
      End If
   Next i
End Sub

On one side, we have the field labels, which will show the name of the currently active database field and on the other we have data labels, which will display content from the database.

As soon as we have more that seven fields in the database, the form will have to be enlarged so that there is enough room to accommodate all the labels.

A very important point here is the saving and loading of the templates: When the fields from a database appear in the active document, the connected database must also be loaded when the document is loaded.

Therefore, we need to save the name of the database in the document. To do this, we have to write a header to the document we are saving. Only then may we save the Text Control data. On loading, the header is read first.

Here is the Visual Basic code to do exactly this:

[Visual Basic 6]
Private Sub mnuSaveTemplate_Click()
   Dim FileID As Long
   Dim lFilePos As Long
   On Error Resume Next
   ' Create a "Save File" dialog box
   CommonDialog1.Filter = "TX Demo (*.ttx)|*.ttx"
   CommonDialog1.DialogTitle = "Save As"
   CommonDialog1.Flags = cdlOFNOverwritePrompt Or cdlOFNHideReadOnly
   CommonDialog1.CancelError = True
   CommonDialog1.ShowSave
   If Err Then Exit Sub
   ' Write Header
   Open CommonDialog1.FileName For Binary As #1
   FileID = FILE_ID
   Put #1, , FileID
   Put #1, , curDBName
   Put #1, , vbCrLf
   lFilePos = loc(1)
   Close #1
   TXTextControl1.Save CommonDialog1.FileName, lFilePos, 3
End Sub

The project files for this sample can be found in the Samples\vb6\MailMerge\Step3 directory.

<<  Step 2 - Printing more than one Record