Custom Project Using Hierarchical Recordsets

Part 1: Creating a Form with a TreeView Control

1. In the left pane of the Database window, click the Forms object button, and click the New button.

2. In the New Form window, choose Design View and click OK. The Form design window opens.

3. Choose Insert | ActiveX Control.

4. In the ActiveX Control box, choose Microsoft TreeView Control 6.0 as shown in Figure 16-8, and click OK to place a TreeView control on the form.

Figure 16-8: The Microsoft TreeView control provides an excellent way to display shaped recordsets in an Access form.

5. Resize the TreeView control and the form to match Figure 16-9.
Figure 16-9: A TreeView control after being placed and resized on the Access form.

Part II

6. Click the TreeView control to select it and choose View | Properties.

7. In the ActiveX Control properties sheet, change the Name property of the TreeView control to myTreeCtrl, as shown in Figure 16-10.

Figure 16-10: You can set the standard properties of the TreeView control by right-clicking the control in the form's Design view and choosing Properties.

Treeview Control Vba

Figure 16-10: You can set the standard properties of the TreeView control by right-clicking the control in the form's Design view and choosing Properties.

8. Close the properties sheet. You should be back in Design view.

9. Choose Edit | TreeCtrl Object and click Properties. Adjust the custom properties of the TreeView control as listed on the General tab in Figure 16-11.

Notice that in addition to the properties access via the Access properties sheet (see Figure 16-10), the ActiveX TreeView control exposes a number of custom properties that can be adjusted via the TreeCtrl Properties window as shown in Figure 16-11.

Figure 16-11: You can set custom properties of the TreeView control in the TreeCtrl Properties window by choosing Edit | TreeCtrl Object.

Excel 2003 Vba Formparent

Save the form as frmOrders.

Save the form as frmOrders.

Creating and Manipulating Databases with ADO

Part 2: Writing an Event Procedure for the Form Load Event

1. In the Form Design view where you placed the TreeView control, choose Edit | Select Form.

2. Choose View | Properties to display the Properties window for the selected form.

3. In the Form window, select the Event tab, and click next to the On Load event property.

4. Click the Build button (...) next to the OnLoad property to display the Choose Builder window.

5. In the Choose Builder window, select Code Builder and click OK. The form module window appears with the following Form_Load event procedure stub:

Private Sub Form_Load() End Sub

6. Write the code for the Form_Load event procedure as shown below:

Private Sub Form_Load()

Dim conn As ADODB.Connection

Dim rstCustomers As ADODB.Recordset

Dim rstOrders As ADODB.Recordset

Dim rstOrderDetails As ADODB.Recordset

Dim fld As Field

Dim objNode1 As Node

Dim objNode2 As Node

Dim strConn As String

Dim strSQL As String

Dim strSQLCustomers As String Dim strSQLOrders As String Dim strSQLOrderDetails As String Dim strSQLRelParentToChild As String Dim strSQLRelGParentToParent As String

' Create the ADO Connection object Set conn = New ADODB.Connection

' Specify a valid connection string strConn = "Data Provider=Microsoft.Jet.OLEDB.4.0;"

strConn = strConn & "Data Source = " & _

CurrentProject.Path & "\Northwind.mdb" conn.ConnectionString = strConn

' Specify the Data Shaping provider conn.Provider = "MSDataShape"

' Open the connection conn.Open

Part II

' Specify SELECT statement for the Grandparent strSQLCustomers = "SELECT CustomerID AS [Cust #]," & _ "CompanyName AS [Customer] " & _ "FROM Customers"

' Specify SELECT statement for the Parent strSQLOrders = "SELECT OrderID AS [Order #]," & _ "OrderDate AS [Order Date]," & _ "Orders.CustomerID AS [Cust #] " & _ "FROM Orders ORDER BY OrderDate DESC"

' Specify SELECT statement for the Child strSQLOrderDetails = "SELECT od.OrderID AS [Order #]," & _ "p.CategoryId AS [Category]," &_ "p.ProductName AS [Product]," & _ "od.Quantity," & _ "od.ProductId," & _ "od.UnitPrice AS [Unit Price]," & _ "(od.UnitPrice * od.Quantity) " & _ "AS [Extended Price] " & _ "FROM [Order Details] od " & _ "INNER JOIN Products p " & _ "ON od.ProductID = p.ProductID " & _ "ORDER BY p.CategoryId, p.ProductName"

' Specify RELATE clause to link Parent to Child strSQLRelParentToChild = "RELATE [Order #] TO [Order #]"

' Specify RELATE clause to link Grandparent to Parent strSQLRelGParentToParent = "RELATE [Cust #] TO [Cust #]"

' Build complete SQL statement for the shaped recordset ' adding aggregate functions for the Grandparent and Parent

strSQL =

"SHAPE(SHAPE{" & strSQLCustomers & "}"

strSQL =

strSQL

&

"APPEND((SHAPE{" & strSQLOrders & "} "

strSQL =

strSQL

&

"APPEND({" & strSQLOrderDetails & "} "

strSQL =

strSQL

&

strSQLRelParentToChild & ") AS rstOrderDetails

strSQL =

strSQL

&

"COUNT(rstOrderDetails.Product) "

strSQL =

strSQL

&

" AS [Items On Order],"

strSQL =

strSQL

&

"SUM(rstOrderDetails.[Extended Price]) "

strSQL =

strSQL

&

" AS [Order Total])"

strSQL =

strSQL

&

strSQLRelGParentToParent & ") AS [rstOrders],"

strSQL =

strSQL

&

"SUM(rstOrders.[Order Total]) "

strSQL =

strSQL

&

AS [Cust Grand Total]"

strSQL =

strSQL

&

") AS rstCustomers"

' Create and open the Grandparent recordset Set rstCustomers = New ADODB.Recordset rstCustomers.Open strSQL, conn

' Fill the TreeView control Do While Not rstCustomers.EOF

Creating and Manipulating Databases with ADO

Set objNodel = myTreeCtrl.Nodes.Add _ (Text:=rstCustomers.Fields(0) & _ " " & rstCustomers.Fields(l) & _ " ($ " & rstCustomers.Fields(3) & ")")

Set rstOrders = rstCustomers.Fields("rstOrders").Value Do While Not rstOrders.EOF

Set objNode2 = myTreeCtrl.Nodes.Add _ (relative:=objNode1.Index, _ relationship:=tvwChild, _ Text:=rstOrders.Fields(0) &_ " " & rstOrders.Fields(l) & _ " " & rstOrders.Fields(4) & " (items)" & _ " $" & rstOrders.Fields(5) &_ " (Order Total)") Set rstOrderDetails = _

rstOrders.Fields("rstOrderDetails").Value Do While Not rstOrderDetails.EOF myTreeCtrl.Nodes.Add _

relative:=objNode2.Index, _ relationship:=tvwChild, _ Text:=rstOrderDetails.Fields(3) & _ " " & rstOrderDetails.Fields(2) &_ " $" & rstOrderDetails.Fields(6) &_ " (" & rstOrderDetails.Fields(3) &_ " x $" & rstOrderDetails.Fields(5) & ")" rstOrderDetails.MoveNext Loop rstOrders.MoveNext

Loop rstCustomers.MoveNext

Loop

' Cleanup rstCustomers.Close

Set rstCustomers = Nothing

Set conn = Nothing

End Sub

When you open the frmOrders form, the Form_Load procedure shown in step 6 populates the TreeView control with the data from the Northwind database. As you can see from Figure 16-12, the results are quite impressive. Clicking on the nodes in the TreeView control expands and collapses the details underneath the node. You can quickly create such hierarchical views of your own data by modifying the Form_Load procedure's SQL statements.

Part II

I frmOrders : Form

ALFKJ Alfreds F;stterkis1e i'î ¿1590.2)

lid* I 1/3/133B 2 (items) 33BÜ (OrdErTatal) 13352 3/16/1933 3 (Items) 1131.3 (Order Total) 10035 ]ji$i1S3B 2 (Hems) 165] {OriferTotal)

2 Orig'nül Frankfurter griina Süße t26 <2 j: 1:1 Ti 15 FtaclattoCourda*aul1 1825 (15xt55) 1D702 13/13/1337 2 (items) (333 (Order Total) 1DE32 ID/3/1937 1 (items) 3B7B (Drde r TotaiJ El 13613 3/35/1937 3 (Items) 11336 (OrderTota[) Fi ANÄTR AnoTrujiLloEmpired ados yhel ados (31132.95) □ AMTOM Antonio W□ ren□ Taqueria (3 7515.33)

El 1D356 1/33/1333 3 (Items) !663 (OrdsrTota!) E 13632 3/35/1937 3 (items) 1375.5 (Order Total)

FthdnbrduKlostEfciEr E23B.5 (3D . £7 75) -1 Louisiana HDt Spiced Okra 663 (1x117) 30 Gedöst £75 (33x£2.6> £ 1DB77 3/22/1937 2 (items) 335B.3 (Order Totol)

0 13573 6/13/1937 3 (items) 13332 (OrderTolaO EB3 13535 5/13/1937 i (items) 12156.5 (Order Totalj t; 1D5Ü7 1/15/1337 2 (itemsl 3BB125 (Order Total)

1 13365 11 /27/1336 1 (item s) 1103 2 (Order Total)

Figure 16-12: The TreeView control is filled with the data from the Northwind database when the user opens the form.

Prior to populating the TreeView control with the data, we connect to the database and enlist the services of the Data Shaping provider:

conn.Provider = "MSDataShape"

Because a TreeView control displays data as a hierachy, we need to build a complex SQL statement using the SHAPE syntax we learned in preceding sections.

To make things easier for ourselves, we start by defining SQL statements with fields we want to display for parent, child, and grandchild recordsets. Notice that we renamed some fields using the AS clause. We also defined separate statements to allow us to link grandparent to parent and parent to child.

The structure we need to create can be illustrated like this:

Grandparent Parent

Child

Now that we've defined the relationship and the fields for our data hierachy, we use the SHAPE commands to build the complete SHAPE statement:

strSQL = "SHAPE(SHAPE{" & strSQLCustomers & "}"

strSQL = strSQL & "APPEND((SHAPE{" & strSQLOrders & "} "

strSQL = strSQL & "APPEND({" & strSQLOrderDetails & "} "

strSQL = strSQL & strSQLRelParentToChild & ") AS rstOrderDetails,"

strSQL = strSQL & "COUNT(rstOrderDetails.Product) "

strSQL = strSQL & " AS [Items On Order],"

strSQL = strSQL & "SUM(rstOrderDetails.[Extended Price]) "

strSQL = strSQL & " AS [Order Total])"

strSQL = strSQL & strSQLRelGParentToParent & ") AS [rstOrders],"

strSQL = strSQL & "SUM(rstOrders.[Order Total]) "

strSQL = strSQL & " AS [Cust Grand Total]"

strSQL = strSQL & ") AS rstCustomers"

Creating and Manipulating Databases with ADO

While creating the SHAPE statement, we added additional calculated fields using the aggregate functions. For instance, in the parent recordset (rstOrders) we calculated the number of items ordered using the COUNT function,

COUNT(rstOrderDetails.Product) AS [Items On Order]

and used the SUM function to obtain the total amount of the order:

SUM(rstOrderDetails.[Extended Price]) AS [Order Total]

In the grandparent recordset (rstCustomers) we used the SUM function to calculate the total amount owed by a customer.

When expanded, the complete SHAPE statement will look as follows:

strSQL

=

"SHAPE(SHAPE{"

strSQL

=

strSQL

&

"SELECT CustomerID AS [Cust #],"

strSQL

=

strSQL

&

"CompanyName AS [Customer]"

strSQL

=

strSQL

&

"FROM Customers"

strSQL

=

strSQL

&

„J,,

strSQL

=

strSQL

&

"APPEND((SHAPE{"

strSQL

=

strSQL

&

"SELECT OrderlD AS [Order #],"

strSQL

=

strSQL

&

"OrderDate AS [Order Date],"

strSQL

=

strSQL

&

"Orders.CustomerlD AS [Cust #]"

strSQL

=

strSQL

&

"FROM Orders "

strSQL

=

strSQL

&

"ORDER BY OrderDate DESC"

strSQL

=

strSQL

&

"J"

strSQL

=

strSQL

&

"APPEND({"

strSQL

=

strSQL

&

"SELECT od.OrderlD AS [Order #],"

strSQL

=

strSQL

&

"p.Categoryld AS [Category],"

strSQL

=

strSQL

&

"p.ProductName AS [Product],"

strSQL

=

strSQL

&

"od.Quantity,"

strSQL

=

strSQL

&

"od.ProductId,"

strSQL

=

strSQL

&

"od.UnitPrice AS [Unit Price],"

strSQL

=

strSQL

&

"(od.UnitPrice * od.Quantity) "

strSQL

=

strSQL

&

"AS [Extended Price] "

strSQL

=

strSQL

&

"FROM [Order Details] od INNER JOIN Products p

strSQL

=

strSQL

&

"ON od.ProductID = p.ProductID "

strSQL

=

strSQL

&

"ORDER BY p.Categoryld, p.ProductName"

strSQL

=

strSQL

&

"J"

strSQL

=

strSQL

&

"RELATE [Order #] TO [Order #]"

strSQL

=

strSQL

&

strSQL

=

strSQL

&

"AS rstOrderDetails,"

strSQL

=

strSQL

&

"COUNT(rstOrderDetails.Product) "

strSQL

=

strSQL

&

"AS [Items On Order],"

strSQL

=

strSQL

&

"SUM(rstOrderDetails.[Extended Price]) "

strSQL

=

strSQL

&

"AS [Order Total])"

strSQL

=

strSQL

&

"RELATE [Cust #] TO [Cust #]"

strSQL

=

strSQL

&

") "

strSQL

=

strSQL

&

"AS [rstOrders],"

strSQL

=

strSQL

&

"SUM(rstOrders.[Order Total]) "

strSQL

=

strSQL

&

"AS [Cust Grand Total]) AS rstCustomers"

Notice that the SHAPE statement we built contains standard fields pulled from the database tables and child recordsets (rstOrders, rstOrderDetails), as well as calculated columns. The rstOrders recordset is a field in the rstCustomers recordset. This field contains order information for a customer. rstOrderDetails

Part II

is a field within the rstOrders recordset. This field contains the order details information for a customer's order.

Now that we've completed the SHAPE statement, we can open the grandparent recordset and begin populating the TreeView control with our data.

A TreeView control consists of Node objects, which you can expand or collapse to display or hide child nodes. Nodes that have child nodes are referred to as parent nodes. The nodes located at the top of the tree control are referred to as root nodes. Root nodes can have sibling nodes that are located on the same level. For example, customer ALFKI (see Figure 16-12) is a root node, and so is the customer ANATR, ANTON, and so on. They are also siblings of one another.

To populate a TreeView control, we use the Add method of the Nodes collection like this:

Set objNode1 = myTreeCtrl.Nodes.Add

The objNode1 above is an object variable representing the Node object. The first node added to a tree view is a root node.

The Add method of the Nodes collection uses the following syntax:

object.Add([relative,] [relationship,] [key], text[, image,] [selectedimage])

In the syntax above, the only required arguments are object and text. The object is the Object variable (myTreeCtrl) representing the TreeView control. The text is a string that appears in the node. Now, looking at the complete statement

Set objNode1 = myTreeCtrl.Nodes.Add _

(Text:=rstCustomers.Fields(0) & _ " " & rstCustomers.Fields(1) & _ " ($ " & rstCustomers.Fields(3) & ")")

we have created a root node to display the following information:

Cust # (rstCustomers.Fields(0)) Customer (rstCustomers.Fields(1)) Cust Grand Total (rstCustomers.Fields(3))

Because the above statement appears inside a looping structure, the TreeView control will display all the customers at their root level.

Now that we've taken care of the root node, we go on to add children and grandchildren. A child node has a relationship to a parent node that has already been added. To define a child node, in addition to the required text argument, we will use two optional arguments of the Add method as described below: relative — This is the index number or key of a preexisting Node object. In our example, we used the index of the parent node that we just created (rel-ative:=objNode1.Index).

Note: When a Node object is created, it is automatically assigned an index number. This number is stored in the Node object's Index property.

Creating and Manipulating Databases with ADO

relationship — Specifies the type of relationship you are creating. Use the tvwChild setting to create a child node of the node named in the relative argument (see above).

The statement that creates a child node looks like this:

Set objNode2 = myTreeCtrl.Nodes.Add _ (relative:=objNode1.Index, _ relationship:=tvwChild, _ Text:=rstOrders.Fields(0) &_ " " & rstOrders.Fields(1) & _ " " & rstOrders.Fields(4) & " (items)" & _ " $" & rstOrders.Fields(5) &_ " (Order Total)")

The above statement displays order information for a customer. The child node text argument is set to display:

Order # (rstOrders.Fields(0)) Order Date (rstOrders.Fields(1)) Items On Order (rstOrders.Fields(4)) Order Total (rstOrders.Fields(5))

Because the statement above appears inside a looping structure, the TreeView control will display the order information for each customer.

Finally, we add grandchildren using the following statement:

myTreeCtrl.Nodes.Add _

relative:=objNode2.Index, _ relationship:=tvwChild, _ Text:=rstOrderDetails.Fields(3) & _ " " & rstOrderDetails.Fields(2) &_ " $" & rstOrderDetails.Fields(6) &_ " (" & rstOrderDetails.Fields(3) &_ " x $" & rstOrderDetails.Fields(5) & ")"

The above statement displays order details for a customer's order. Notice that this Node object references the index number of the child object that has just been added (relative:=objNode2.Index).

The grandchild node text argument is set to display:

Quantity (rstOrderDetails.Fields(3))

Product (rstOrderDetails.Fields(2))

Extended Price (rstOrderDetails.Fields(6))

Quantity x Unit Price (rstOrderDetails.Fields(3) & "x $" &

rstOrderDetails.Fields(5))

The looping structure ensures that the order details above are listed for all customers' orders.

Now that you are done with this custom project, you should be able to provide your own hierarchical data in a pretty neat user interface.

Part II

0 0

Post a comment