Article ID: 251329
Article Last Modified on 7/1/2004
APPLIES TO
- Microsoft Visual Basic 6.0 Professional Edition
- Microsoft Visual Basic 6.0 Enterprise Edition
- Microsoft ActiveX Data Objects 2.1
- Microsoft ActiveX Data Objects 2.1 Service Pack 1
- Microsoft ActiveX Data Objects 2.1 Service Pack 2
- Microsoft ActiveX Data Objects 2.5
- Microsoft ActiveX Data Objects 2.6
- Microsoft ActiveX Data Objects 2.7
This article was previously published under Q251329
SUMMARY
This article demonstrates how to base a DataReport on a hierarchical DataEnvironment and refresh the report after changing the command text.
MORE INFORMATION
The following Microsoft Knowledge Base article illustrates how to refresh a DataReport when re-executing a parameterized command:
244779 How To Refresh a Parameterized DataReport
This article demonstrates a similar technique, which modifies the command itself.
NOTE: Any modification to the command cannot change the shape of the hierarchy or the names of the fields.
In the sample program provided in this article, the command is modified to show the following:
- All orders where the CustomerID starts with the letter "A".
- All orders shipped through shipper #1.
- All orders placed in 1996.
To create the sample, use these steps:
- Open a new Standard EXE Project in Microsoft Visual Basic. Form1 is created by default.
- Add a DataEnvironment (DataEnvironment1) and a DataReport (DataReport1) to the project.
- Add a Connection (Connection1) to the DataEnvironment that points to the SQL Server Northwind database using the OLE DB Provider for SQL Server.
- Add a Command (Command1) to Connection1. Right-click the command button and then select Properties.
- In the Properties dialog box, click the General tab and add the following:
SQL Statement: SELECT Customers.CompanyName, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
- In the Properties dialog box, click the Grouping tab and set the following:
Group Command Object: (select this item)
Grouping Command Name: Customer
Fields Used for Grouping: CustomerID - In the Properties dialog box, click the Aggregates tab. Click Add and then edit the following properties:
Name: Company
Function: Any
Aggregate On: Grouping
Field: CompanyName Click OK to close the Properties dialog box. The hierarchy changes to the following:
Command1 grouped using Customer Summary fields in Customer CustomerID Company Detail fields in Command1 CompanyName OrderID CustomerID EmployeeID ...- In the DataReport, set the following properties:
DataSource: DataEnvironment1
DataMember: Customer - Right-click the body of the report, and then select the Insert Group Header/Footer menu item.
- Drag the following fields from the DataEnvironment into the Detail section of the DataReport:
OrderID
CustomerID
OrderDate
ShipViaDrag the following fields from the DataEnvironment into the Group Header section of the DataReport:
CustomerID
Company Add three command buttons (Command1, Command2, Command3) and the following code to the form:
Option Explicit Const preSHAPE = "SHAPE {" Const SQL = "SELECT Customers.CompanyName, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID" Const postSHAPE = "} AS Command1 COMPUTE Command1, ANY(Command1.'CompanyName') AS Company BY 'CustomerID'" Private Sub Command1_Click() ' ' Selects orders with CustomerID starting with A. ' Load DataEnvironment1 With DataEnvironment1 If .rsCustomer.State Then .rsCustomer.Close .Commands!Customer.CommandText = preSHAPE & SQL & " WHERE Customers.CustomerID LIKE 'A%'" & postSHAPE .Customer End With DataReport1.Refresh If DataReport1.Visible = False Then DataReport1.Show End Sub Private Sub Command2_Click() ' ' Selects orders shipped via Shipper #1 ' Load DataEnvironment1 With DataEnvironment1 If .rsCustomer.State Then .rsCustomer.Close .Commands!Customer.CommandText = preSHAPE & SQL & " WHERE ShipVia=1" & postSHAPE .Customer End With DataReport1.Refresh If DataReport1.Visible = False Then DataReport1.Show End Sub Private Sub Command3_Click() ' ' Selects orders in 1996 ' Load DataEnvironment1 With DataEnvironment1 If .rsCustomer.State Then .rsCustomer.Close .Commands!Customer.CommandText = preSHAPE & SQL & " WHERE Year(OrderDate)=1996" & postSHAPE .Customer End With DataReport1.Refresh If DataReport1.Visible = False Then DataReport1.Show End Sub Private Sub Form_Load() Command1.Caption = "CustomerID starts with 'A'" Command2.Caption = "Orders via Shipper #1" Command3.Caption = "1996 Orders" End SubYou may have to modify the SHAPE statement in the code based on your hierarchical command. You can copy the Command1 SHAPE statement by right-clicking it in the DataEnvironment and then select Hierarchy Info. You can paste the SHAPE statement in the code and use it to modify the preSHAPE, postSHAPE, and SQL constants. The DataEnvironment Command and Recordset names used in the preceding code, in this case "Customer" and "rsCustomer", match the name that displays in the top level of the hierarchy.
- Save the project and run it. Click each of the command buttons. You see the DataReport show data based on the modified command text, namely:
- Orders where the CustomerID starts with "A".
- Orders shipped by shipper #1.
- Orders placed in 1996.
REFERENCES
For additional information regarding the SHAPE syntax, click the article numbers below to view the articles in the Microsoft Knowledge Base:
189657 How To Use the ADO SHAPE Command
185425 INFO ADO Hierarchical Recordsets via SHAPE APPEND w/C++/VBA/Java
249097 How To Re-Shape a Hierarchical Recordset
247662 How To Access a Secured Jet Database w/ the MSDataShape Provider
Keywords: kbhowto kbdatabase KB251329
