Option Explicit

Private Sub DataReport_Initialize()
    
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim Sql, strSql As String
    
    '连接数据库(Access)
    Set cn = New ADODB.Connection
    With cn
        .Provider = "MSDataShape.1"     '一定要这句
        .Open "Data Provider=Microsoft.Jet.OLEDB.4.0 ;" & _
            "Data Source=" & App.Path & "\db1.mdb;" & _
            "Persist Security Info=False"
    End With
    
    '查询表：
    Sql = "SELECT Coid,Caption,Qty FROM tt "
     
    '用SHAPE命令返回查询父子记录集
    strSql = "SHAPE {" & Sql & " } " & _
        "AS rst COMPUTE sum(rst.Qty) as Qty,rst BY Coid "
    Set rs = New ADODB.Recordset
    rs.Open strSql, cn, adOpenStatic, adLockReadOnly

    
    '设置RptTextBox控件属性
    With RptGroup
        '分组标头
        .Sections("Section6").Controls("text1").DataField = "Coid"
        '.Sections("Section6").ForcePageBreak = 1 '强制分页（不要求按组分页可以注释掉）
        
        '分组注脚--分组合计
        .Sections("Section7").Controls("text5").DataField = "Qty"
        
        '注意以上分组信息不要设置控件的DataMember属性
        
        '细节标头,其中“rst”是SHAPE命令中分组子集名称
        .Sections("Section1").Controls("text2").DataField = "Caption"
        .Sections("Section1").Controls("text2").DataMember = "rst"
        .Sections("Section1").Controls("text3").DataField = "Coid"
        .Sections("Section1").Controls("text3").DataMember = "rst"
        .Sections("Section1").Controls("text4").DataField = "Qty"
        .Sections("Section1").Controls("text4").DataMember = "rst"
        
        '页注脚--本页合计
        .Sections("Section5").Controls("Function1").DataField = "Qty"
        .Sections("Section5").Controls("Function1").FunctionType = 0
    End With
    
    
    '绑定到报表
    Set Me.DataSource = rs
   
End Sub