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
lblField(i).Top = 320 + (i * 300)
lblField(i).Caption = rs.Fields(i).name
lblField(i).Visible = True
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
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
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)
TXTextControl1.Save CommonDialog1.FileName, lFilePos, 3
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/
The Newsletter Team