HomeDownloadsSource Code LibraryDatabases (5)How to get data from a database into a report

How to get data from a database into a report

Author:	TX Text Control Support Department
Language:	Visual Basic
Version:	1.1
Released:	March 20, 2001
Last modified:	January 11, 2008
Requirements:	TX Text Control ActiveX with Visual Basic

TX Text Control's ability to display tables and access the individual table cells from code make it an ideal tool for creating database reports. Its' decimal and right-aligned tabs, as well as proper alignment of negative numbers indicated by brackets, allow for clean and readable formatting of large columns of numbers. Besides these essential features, TX Text Control lets you enhance your report with images, and OLE objects, and all its various formatting options.

This sample program shows you how to create and format a report from a database, and add table captions that are repeated at the top of every page.

The program consists of a simple form that contains a TX Text Control and a menu. In the File menu, an Access database can be selected, for instance the "nwind.mdb" database that is shipped with VB. The database is opened and displayed in a TX Text Control table. Once loaded, the report can be printed using the File/Print menu item.

Connecting to the database: There are several ways to get the data from a record to a table cell. In this example we open the database using DAO code, and transfer the data using TX Text Control's TableCellText property. This approach is easily portable to SQL or other types of database connections.

TableRow = 1
While Not rs.EOF
  For n = 0 To rs.Fields.Count - 1
    If Not IsNull(rs(n)) Then
      TXTextControl1.TableCellText(MAINTABLEID, TableRow, _
      n + 1) = rs(n)
    End If
  Next n
  rs.MoveNext
  TableRow = TableRow + 1
Wend

If the database contains formatted text, say HTML or RTF data, the TableCellStart property and LoadFromMemory method can be used instead of the TableCellText property.

Creating repeated table headers: TX Text Control's tables do not have the built-in ability to automatically repeat a table header on every page, but we can create these with page headers:

TXTextControl1.HeaderFooter = txHeader
TXTextControl1.HeaderFooterActivate txHeader
TXTextControl1.TableInsert 1, rs.Fields.Count, 0, HEADERTABLEID
For n = 0 To rs.Fields.Count - 1
  TXTextControl1.TableCellText(HEADERTABLEID, 1, n + 1) = _
  rs.Fields(n).Name
Next n

In brief, that is how it works. The complete sample program also lets you send the report to a printer and select a table and range of records before loading the database.