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 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

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: