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.