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
|