Tell a Friend, Mail Merge Part 3

This week we are getting back to our top topic of mail merge. This is the third part of the three part tutorial.

Last week, we added a print queue to our mail merge application. This week we are going to build a real mail merge layout editor into our little sample program. In other words, the user should be able to select a database and insert its fields into his active document just by clicking his mouse. Then, when we start the actual mail merging process, these fields will be replaced by the data from the database.

So, what is so difficult about that? Well, 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.

First of all, we set up a global variable curDBName. In this memory we are going to write the name of the currently loaded database.

Secondly, we are going to set up a menu point, 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 curDBName. Storing an entire database in the memory of a PC 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. So, how do we do this? We use a combo box. On loading the database we then 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.

where ever we had static code, we now have to replace it with dynamically produced code.

Take for example 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.

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.

We therefore 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 TX Text Control data. On loading, the header is read first.

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

Please note that for this code to work, the database has to be in the same folder as our application. Please feel free to improve on this example. Any improvements you may make, you can certainly send to us and we will copy them onto your web site to share with the rest of the TX Text Control Community.

Delphi samples from this week, and the last two, will be published in the next edition of this newsletter.

Two Worthwhile Programmer Sites

One of the TX Text Control Webmaster's many duties is to track down web pages where we can publish a link to www.textcontrol.com. This week, we came across a magnificent site. In fact, we would even goes as far as to say that this is probably the Internet's most complete source of free downloadable programming files, source codes, utilities, Visual Basic, Delphi, C/C++, graphics, Windows, Java, and other tools for programmers and developers. There are well over 8200 files and 2300 links organized in an easy-to-find format! You really must NOT miss this site.

However, those of you who are just starting our with Visual Basic may be a bit perplexed at the sites' enormous source of software. For you, we have another great Visual Basic site. Go and have a look at 'Andy's Visual Basic Site'

There you will find a wide range of easy to understand Tutorials, API Calls, Samples. And if you can't find the answer there you can contact Andy or post a message at his forum, which is visited by 100's of people every week. For beginners and advanced users alike, 'Andy's Visual Basic Site' is a great resource.

Top TX Tip: Meik's Mail Merging - The Professional Way (Part #3)

Two Worthwhile Programmer Sites