This message contains graphics. If you do not see the graphics: Graphic Version.
 
Tell a Friend, Mail Merge Part 3

Top TX Tip: Meik's Mail Merging - The Professional Way (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.

So, let's have a look at what we need to do, step-by-step:

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.

Taking the samples from last week (and the week before):

  • Mail Merge Tutorial Part #1
  • Mail Merge Tutorial Part #2

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.

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

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.

Here is the Visual Basic code to do exactly this:

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

As usual, you can download the entire Visual Basic code from our zip file:

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.

Visit them at: http://www.programmersheaven.com

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.

Visit them at: http://andysvb.cjb.net/


Best regards

The Newsletter Team

Text Control GmbH respects your online time and privacy. We only send this newsletter to TX Text Control customers and people who have signed up to receive it. However, if you would prefer not to receive future issues of the newsletter, you may unsubscribe at any time. If you received this newsletter forwarded from a colleague or friend, you may wish to subscribe directly.

Sent to: N/A.

Imprint | Unsubscribe | Subscribe

© 2019 Text Control GmbH. All Rights Reserved.