How To Dynamically Populate a Group Data Report in Visual Basic (289793)

MORE INFORMATION

Step-by-Step Example

1. Open a Standard EXE project in Visual Basic. Form1 is created by default.
2. Add a Command button to Form1.
3. On the Project menu, click References, and then add a reference to Microsoft ActiveX Data Objects Library.
4. On Project menu, click to add DataReport1. If Add Datareport is not on the Project menu, add it from the Designers tab located on the Project menu under Components. (Make sure that you deselect Selected Items Only in the Project menu under Components.)
5. Open Datareport1 and ensure that the Detail, Page Header, and Page Footer sections are visible. If the Report Header or Footer sections are visible, remove them by right-clicking the report and removing the check mark from Show Report Header/Footer.
6. Right-click and select Insert Group Header/Footer. A new Section, Section4, is added to the Datareport.
7. Add one rptLabel control and one rptTextbox control to the new section.
8. Add two rptLabel controls and two rptTextbox controls to the Detail section of the Datareport.
9. Copy and paste the following code into the form:

         Dim cn As New ADODB.Connection
         Dim rs As New ADODB.Recordset
         Dim cmd As New ADODB.Command
         Dim rs1 As New ADODB.Recordset
         Private Sub Command1_Click()
         Dim q As Integer
         Dim intCtrl As Integer
         Dim x As Integer
         Dim z As Integer
         x = 0
         q = 0
         z = 0
   
         With DataReport1
         .Hide
         Set .DataSource = rs
         .DataMember = ""
         With .Sections("section4").Controls
         For intCtrl = 1 To .Count
         If TypeOf .Item(intCtrl) Is RptLabel Then
            .Item(intCtrl).Caption = "City" & " :"
             q = q + 1
         End If
         If TypeOf .Item(intCtrl) Is RptTextBox Then
           .Item(intCtrl).DataMember = ""
           .Item(intCtrl).DataField = "City"
         End If
         Next
         End With
         
         q = 0
         With .Sections("Section1").Controls
             For intCtrl = 1 To .Count
             If TypeOf .Item(intCtrl) Is RptLabel Then
                      .Item(intCtrl).Caption = rs1.Fields(q).Name & " :"
                       q = q + 1
             End If
             If TypeOf .Item(intCtrl) Is RptTextBox Then
                      .Item(intCtrl).DataMember = "Command1"
                      .Item(intCtrl).DataField = rs1(z).Name
                      z = z + 1
             End If
         Next intCtrl
         End With
         .Refresh
         .Show
         End With
         End Sub
   
         Private Sub Form_Load()
   
         Command1.Caption = "Show Report"
         cn.Open "Provider=MSDATASHAPE; Data Provider=Microsoft.JET.OLEDB.4.0;" & _
                  "Data Source=D:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;"
          
         With cmd
              .ActiveConnection = cn
              .CommandType = adCmdText
              .CommandText = " SHAPE {SELECT FirstName,Lastname,City FROM `Employees`}  AS Command1 COMPUTE Command1 BY 'City'"
              .Execute
         End With
          
         With rs
              .ActiveConnection = cn
              .CursorLocation = adUseClient
              .Open cmd
         End With
         Set rs1 = rs(0).Value
   
         End Sub