|
DAO Example to create DB-Description |
  
|
|
DAO Example to create DB-Description |
|
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
