This message contains graphics. If you do not see the graphics: Graphic Version.
Creating Reports with TX Text Control

In this newsletter, we are going to show you how to build a report designer with TX Text Control. It has separate windows for header, footer and report data. Variables can be entered in any of the 3 windows, which are later filled in from a database. Besides displaying data, fields can also be used to perform simple calculations. All of TX Text Control's formatting options are available to make the report visually appealing.

The Northwind database which is included with Visal Studio 6 is used as a sample database.

TX Text Control ActiveX v10 (trial or full) and Visual Basic 6 are required for this sample application.

Quick users guide

After starting the Report application, select File/Open and load the sample report designer template named nwindarticle.txr.

Click on the button to generate a report from the template and the attached Northwind sample database. The report will be displayed in a seperate child window.

The report can be saved as an Adobe PDF file by selecting the File / Export to pdf menu item, or can be printed. Although not shown in this sample program, it would be as esay to save a report in Microsoft Word, HTML or RTF format. The following screenshot shows the sample report saved as a PDF file and displayed in Acrobat Reader.

If you wish to design your own reports, select File/New from the menu. Use the Report/Open Database menu item to select the desired table or query from the nwind.mdb.

Add some database fields by selecting their field name in the toolbar and pressing the Add button.

To add a sum field you have to select a database field in the template and press the Sum button in the toolbar. The field's name will change to sum("field name").

You can also add a page counter anywhere in the report. Set the caret to the desired text position and press the double cross button in the toolbar. A field named page$ will be inserted.

For additional formatting there is a context menu. Press the right mouse button in the designer to activate it.

Programming details

The program is an MDI application which has two different types of child forms. The first one, frmEditLayout, is a simple report designer in which you can add database fields, tables and static text to the three areas of a report. The second form named frmPreview will display the generated document.

When opening the sample document via the File Open menu item the program opens the designer form and the corresponding database, the nwind.mdb located in your Visual Basic 6 bin directory. If the database could not be found it opens a file open dialog where you can specify the location of the database.

How it works

A report is created in three steps:

  1. Fields contained in the header are replaced by data from the respective database fields. The header is then copied to the preview control.

  2. The template from the middle part of the designer form is used to create an entry for each record in the database.

  3. The footer is added to the document.

A function similar to the one used in the MailMerge sample program replaces the field contents with data from the database:

Sub Merge(rs As Recordset, TXDest As TXTextControl, _ Sums As Collection, orgdata As Variant) Dim id As Integer Dim i As Integer Dim data As String Dim TypeID As DAO.DataTypeEnum Dim FieldName As String Dim sumField As SumaryField ' reload original content TXDest.ResetContents TXDest.LoadFromMemory orgdata, 3, False id = TXDest.FieldNext(0, 0) ' Now fill all fields with data While id <> 0 FieldName = TXDest.FieldData(id) If FieldName = ^"page$^" Then data = CStr(frmEditLayOut.numPages) ElseIf Left$(FieldName, 4) = ^"sum(^" Then ' summary field found Set sumField = Sums(Mid(FieldName, 5, Len(FieldName) - 5)) data = FormatData(sumField.value, sumField.DataType) Else data = FormatData(rs(FieldName), rs(FieldName).Type) End If TXDest.FieldCurrent = id TXDest.FieldText = data TXDest.FieldDelete False ' remove the fields to increase insertion speed id = TXDest.FieldNext(0, 0) Wend ' Next i End Sub

The function handles three types of fields:

  • Page number
  • Sum
  • Database field

Sum fields are updated for each database record in turn, so the value of these fields is always the sum of all records up to the current row of the recordset. Database fields are simply replaced by the contents of the current record. To increase the performance of the report engine, all fields are converted to plain text before they are appended to the report. This is done by by calling FieldDelete with False as a parameter. The original contents is saved and loaded back in for each database record. This is done in the DoReport function:

Public Sub DoReport() On Error GoTo err_Report Dim headerheight, footerheidth, dataheight As Long Dim docheight As Long Dim ext() As Long Dim Sums As New Collection numPages = 1 bCancel = False ... ' save the original content of the report BackupTXFields orgHeaderContent, orgDataContent, orgFooterContent ' summary field found CheckForSumFields Sums frmPreview.TXTextControl1.ResetContents frmPreview.TXTextControl1.Visible = False Merge rs, TXHeader, Sums, orgHeaderContent ext = TXHeader.GetTextExtent() headerheight = GetTextHeight(TXHeader) InsertHeader footerheight = GetTextHeight(TXFooter) ' calculate the remaining document height docheight = frmPreview.TXTextControl1.PageHeight _ - frmPreview.TXTextControl1.PageMarginT _ - frmPreview.TXTextControl1.PageMarginB - headerheight While (rs.EOF = False And bCancel = False) UpdateSums Sums, rs Merge rs, TXData, Sums, orgDataContent data = TXData.SaveToMemory(3, False) ext = TXData.GetTextExtent() dataheight = GetTextHeight(TXData) ' does the footer fit on the page ? docheight = docheight - dataheight ' does the footer fit on the page ? If (docheight) <= footerheight Then Merge rs, TXFooter, Sums, orgFooterContent Merge rs, TXHeader, Sums, orgHeaderContent DoNextPage ' reset the remaining document height docheight = frmPreview.TXTextControl1.PageHeight _ - frmPreview.TXTextControl1.PageMarginT _ - frmPreview.TXTextControl1.PageMarginB _ - headerheight - dataheight End If b = frmPreview.TXTextControl1.LoadFromMemory(data, 3, True) rs.MoveNext ... Wend ... RestoreTXFields orgHeaderContent, orgDataContent, orgFooterContent Exit Sub err_Report: MsgBox Err.Description End Sub

Some lines of code which handle the display of a progress dialog, but have no effect on the functionality of the report generation, have been omitted for clarity.

The LoadFromMemory method is used to insert document parts into the generated report. By calling the GetExtent method we can determine the height of the inserted part. This is required to calculate the number of database records fitting on one page. If the page end is reached the DoNextPage function is called which appends the footer to the current page, inserts a page break and adds a new header on the following page.

We wish you a lot of success with this sample application. As always, we are delighted to help you with any matter relating to TX Text Control. Call us or e-mail us! Full contact details at:

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

© 2020 Text Control GmbH. All Rights Reserved.