写过一个示例,参考一下: '=================== DataReport报表示例 ==============='一.要求:查询数据库中内容,每页显示5行数据,有页合计。纸张规格 24.2cm * 11.60cm '二.分析:DataReport好象没有页合计功能,而且每页5行数据的设置比较困难,虽然可以通过调节 ' 边距来达到目的,但因系统、软硬件的设置,可能会在实际应用中出现偏差。要实现 ' 上述二项功能,分组是一个手段 '三.思路:先导出表中数据,加上分组字段(5条数据一组)后导入一个新表。用ADO的SHAPE命令 ' 实现分组和统计。隐藏分组标头的信息,设置分组标头的ForcePageBreak = 1,实现 ' 每页5条数据的显示'====================================================================================='以下代码要选择“工程\引用”命令,引用“Microsoft AetiveX Date Objects 2.x Library”Option ExplicitPrivate Sub DataReport_Initialize() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim Sql As String, strSql As String Dim gID As Integer, i As Integer
'连接数据库,打开记录集 Set cn = New ADODB.Connection Set rs = New ADODB.Recordset
'=================== DataReport报表示例 ==============='一.要求:查询数据库中内容,每页显示5行数据,有页合计。纸张规格 24.2cm * 11.60cm
'二.分析:DataReport好象没有页合计功能,而且每页5行数据的设置比较困难,虽然可以通过调节
' 边距来达到目的,但因系统、软硬件的设置,可能会在实际应用中出现偏差。要实现
' 上述二项功能,分组是一个手段
'三.思路:先导出表中数据,加上分组字段(5条数据一组)后导入一个新表。用ADO的SHAPE命令
' 实现分组和统计。隐藏分组标头的信息,设置分组标头的ForcePageBreak = 1,实现
' 每页5条数据的显示'====================================================================================='以下代码要选择“工程\引用”命令,引用“Microsoft AetiveX Date Objects 2.x Library”Option ExplicitPrivate Sub DataReport_Initialize()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Sql As String, strSql As String
Dim gID As Integer, i As Integer
'连接数据库,打开记录集
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Provider = "MSDataShape" '一定要这句,因为要用到ADO的SHAPE命令
cn.Open "Data Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & App.Path & "\Northwind.mdb;Persist " _
& "Security Info=False;Jet OLEDB:Database Password=zxcvbnm"
rs.CursorLocation = adUseClient
Sql = "select product,price,qty from tb"
rs.Open Sql, cn, adOpenKeyset, adLockOptimistic
'将信息添加分组字段导入临时表
Sql = "delete from temp_tb"
cn.Execute Sql '先删除临时表中信息
gID = 0
For i = 0 To rs.RecordCount - 1
If i Mod 5 = 0 Then gID = gID + 1 '5条信息用一个值,以便按gID分组
Sql = "insert into temp_tb(group_id,product,price,qty) values " & _
"(" & gID & ",'" & rs!product & "'," & rs!price & "," & rs!qty & ")"
cn.Execute Sql
rs.MoveNext
Next
Set rs = Nothing
'用SHAPE命令打开分组父子记录集
Sql = "select group_id,product,price,qty from temp_tb"
strSql = "SHAPE {" & Sql & " } " & _
"AS mGroup COMPUTE sum(mGroup.price) as price," & _
"sum(mGroup.qty) as qty, mGroup BY 'group_id' "
Set rs = New ADODB.Recordset
rs.Open strSql, cn, adOpenStatic, adLockReadOnly
'设置报表数据源
Set Rpt.DataSource = rs
'横向打印
Rpt.Orientation = rptOrientLandscape
'设置报表控件属性
'分组标头,不用设置DataMember
Rpt.Sections("Section6").Controls("text4").DataField = "group_id"
Rpt.Sections("Section6").Controls("text4").Visible = False '隐藏分组信息
Rpt.Sections("Section6").ForcePageBreak = 1 '强制分页
'细节标头
Rpt.Sections("Section1").Controls("text1").DataMember = "mGroup"
Rpt.Sections("Section1").Controls("text1").DataField = "product"
Rpt.Sections("Section1").Controls("text2").DataMember = "mGroup"
Rpt.Sections("Section1").Controls("text2").DataField = "price"
Rpt.Sections("Section1").Controls("text3").DataMember = "mGroup"
Rpt.Sections("Section1").Controls("text3").DataField = "qty"
'分组注脚,页合计。不用设置DataMember
Rpt.Sections("Section7").Controls("text5").DataField = "price"
Rpt.Sections("Section7").Controls("text6").DataField = "qty"
Rpt.Sections("Section7").Controls("label1").Caption = "本页合计"
'页标头,不要每页显示的话也可以放在报表标头中
Rpt.Sections("Section2").Controls("Label2").Caption = "库存报表"
Rpt.Sections("Section2").Controls("Label5").Caption = "报表日期:" & Date
Rpt.Sections("Section2").Controls("Label7").Caption = "产品名称"
Rpt.Sections("Section2").Controls("Label8").Caption = "价 格"
Rpt.Sections("Section2").Controls("Label9").Caption = "库存数量"
'页注脚
Rpt.Sections("Section3").Controls("Label3").Caption = "制表:张三"
Rpt.Sections("Section3").Controls("Label4").Caption = "审核:李四"
'报表注脚总计信息:
Rpt.Sections("Section5").Controls("Label6").Caption = "总 计"
Rpt.Sections("Section5").Controls("Function1").DataField = "price"
Rpt.Sections("Section5").Controls("Function2").DataField = "qty"
End Sub