DAO Example to create DB-Description

[Top]  [Chapter]  [Previous]  [Next]

To start simple we only create a list from items in table "Orders". This would be already sufficient to create an invoice for a certain customer.

 

We create nested groups for each of the tables. Inside each group we add the possible fields.

 

The field names can be usually read easily from the table object. After the structure was created it is used to create a reporting template. The user can modify the template and add fields which are listed in the "repository".

 

Example written in MS Access:

 

Initialization of the editor:

 

Private Sub Form_Load()

' Initialize License

' WPDLLInt1.EditorStart 'licensename', 'licensekey'

 

' load the PCC file

 WPDLLInt1.SetLayout ".\buttons.pcc", "default", "", "main", "main"

 ' We need the 'double editor' mode, toolbar, tables and reporting

 ' If this initialization is missing 'Set Report = td.Report' will fail

 ' Editor 1 gets a ruler and scrollbars

 ' Editor 2 gets scrollbars, navigation and view panels

 WPDLLInt1.SetEditorMode 1, 2 + 8 + 16 + 32, 2 + 4 + 8, 4 + 8 + 128 + 258

' Select normal page layout

 WPDLLInt1.SetLayoutMode 0, 0, 100

End Sub

 

Procedure which takes one database table and creates a simple template to list all fields except for "ID"

 

Private Sub Recreate_Template_Click()

Dim td As WPDLLInt

Dim Report As IWPReport

Dim db As DAO.Database

Dim tdf As DAO.Recordset

Dim i As Integer

Dim mode As Integer

Dim tblNameToLoop As String

Dim fieldName As String

Dim fieldCount As Integer

Set db = CurrentDb() 'pointer to current database

Set td = WPDLLInt1.Object

 

Set Report = td.Report

' the next line fails if reporting as not been activated in Form_Load()

Report.Clear

 

' Create a table of all fields in table 'CUSTOMERS'

tblNameToLoop = "ORDERS"

Report.AddGroup tblNameToLoop, "", tblNameToLoop, "*", 0, ""

Set tdf = db.OpenRecordset(tblNameToLoop, dbOpenSnapshot, dbSeeChanges)

fieldCount = tdf.Fields.Count

For i = 0 To fieldCount - 1

 fieldName = tdf.Fields(i).NAME

' we can deselect some of the fields which are used for the table relation

If InStr(fieldName, "ID") > 0 Then

    mode = 2 ' Deselected but visible.

    Else

    mode = 0 ' Standard: Visible and Selected

End If

  Report.AddField tblNameToLoop + "." + fieldName, fieldName, "", _

   "*", "", "", "", 0, mode, 0

Next

tdf.Close

 

' Add group variables here (if required)

' ...

 

' We need bands, otherwise the group is empty!

Report.AddBand "*", "Header", 1, 0, "", "", 0, 0

Report.AddBand "*", "Data", 0, 0, "", "", 0, 0

Report.AddBand "*", "Footer", 2, 0, "", "", 0, 0

 

db.Close

 

' Init the repository and create a reporting template

Report.InitTemplate "@CUSTOMERS LIST", 5

End Sub

 

If you need calculation, for example to create an invoice you can add group variables.

 

' Group Variable to calculate the price in each row

Report.AddVar "ORDER_PRICE", "Sub Total", "Price * Amount", "*", _

"=0", "CUR=$", "", 0, "=ORDERS.AMOUNT*ORDERS.PRICE", 0

' Group Variable to calculate the total price, note the '+=' and mode=2

' This variable should be use in the footer

Report.AddVar "TOTAL_PRICE", "Total", "Sum of all ORDER_PRICE", "*", _

"=0", "CUR=$", "", 2, "+=ORDERS.AMOUNT*ORDERS.PRICE", 0

 

Group variables are processed before each time the group is used. The StartFormula is only executed before the first time. By default a variable has the value 0 (the formula "=0" is redundant). In the LoopFormula use += to sum up values.

References to table values are possible in formulas, for example "ORDERS.AMOUNT". Since TextDynamic does not access the database directly, the value must be provided using the event OnReadFormulaVar.

Note: If in a report template a field uses the name of a variable, that variable is assigned.

 

To show the report editor without recreation of the template use this code:

 

Private Sub Edit_Template_Click()

 Dim td As WPDLLInt

 Set td = WPDLLInt1.Object

 td.Report.InitTemplate "@Field and Bands", 6

End Sub

 

Create the Report in MS Access

 

Using the integrated support for DAO interfaces, a list can be created with just a few additional lines. Basically only a record set has to be created and assigned to Report.Recordset.

 

Private Sub Create_Report_Click()

Const strQueryName = "ORDERS Query"

Dim db As Database

Dim td As WPDLLInt

Dim RepRS As Recordset

Set td = WPDLLInt1.Object

Set db = CurrentDb() ' Open pointer to current database

Set RepRS = db.OpenRecordset(strQueryName) ' Open recordset on saved query

 

td.Report.Recordset = RepRS

 If td.Report.SetAutomatic(1, "") Then

    td.Report.CreateReport

 End If

 RepRS.Close

 db.Close

End Sub

 

clip0115


[acollectgroupsandfields.htm]    Copyright © 2007 by WPCubed GmbH