手工显示合计行（列汇总） 我们都知道如何操作，可参考：
http://www.access-cn.com/info/3587-cn.html
合计行可选的聚合函数
http://www.access-cn.com/info/3588-cn.html
那如何VBA代码设置表和查询的数据表视图中的合计行（汇总行）
1.VBA代码设置表的数据表视图中的合计行：
db.TableDefs("table1").Properties("TotalsRow") = True
Public Function IsTotalsRowVisible(sTableName As String) As Boolean
Dim db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb()
set td = db.TableDefs(sTableName)
IsTotalsRowVisible = (td.Properties("TotalsRow") = True)
End Function
Public Function IsTotalsRowVisible(sTableName As String) As Boolean
Dim db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb()
Set td = db.TableDefs(sTableName)
On Error Resume Next
IsTotalsRowVisible = (td.Properties("TotalsRow") = True)
' if the TotalsRow property does not exist then return false
If (Err = 3270) Then
IsTotalsRowVisible = False
ElseIf (Err <> 0) Then
Stop
End If
On Error GoTo 0
End Function
Sub test()
Dim db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb()
' test all non-system tables
For Each td In db.TableDefs
If (Not (td.Name Like "MSys*")) Then
Debug.Print td.Name, IsTotalsRowVisible(td.Name)
End If
Next
db.Close
Set db = Nothing
Set td = Nothing
End Sub
2. 用代码打开或关闭查询的数据表视图的合计行 total row:
Set qrydef = CurrentDb.QueryDefs("MyQueryName")
qrydef.Properties.Append qrydef.CreateProperty("TotalsRow", dbBoolean, True) '可以再加个判断，如果属性存在，则直接 qrydef.Properties("TotalsRow")=true
qrydef.Fields(1).Properties("AggregateType").Value = 0 'add the Aggregate Total on field 1
qrydef.Fields(2).Properties("AggregateType").Value = 1 'add the Average Total on field 2
qrydef.Fields(3).Properties("AggregateType").Value = 2 'add the Count Total on field 3
相关代码：
Function LoopQuery(ByVal tempProj As Double)
Dim strSQL As String
Dim qdfTemp As QueryDef
Dim ProjNumb As Double
ProjNumb = tempProj
strSQL = "sql code...."
With CurrentDb
Set qdfTemp = .CreateQueryDef(ProjNumb, strSQL)
qdfTemp.Fields(21).Properties("AggregateType").Value = 0 'add the Aggregate Total on Total Hours
qdfTemp.Fields(23).Properties("AggregateType").Value = 0 'add the Aggregate Total on Total Costs
qdfTemp.Fields(24).Properties("AggregateType").Value = 0 'add the Aggregate Total on Total Discounted
End WithSet qdfTemp = .CreateQueryDef(ProjNumb, strSQL)
qdfTemp.Properties.Append _
qdfTemp.CreateProperty("TotalsRow", dbBoolean, True)Dim pSQL as String
set pSQL = "...."
Dim db As DAO.Database ' database decleration
Set db = CurrentDb ' use current
Dim qdf As QueryDef 'for the query sourceobject
Set qdf = CurrentDb.CreateQueryDef("qtemp", pSQL) ' make QueryDef
Dim prop As DAO.Property ' property for the fields
'At this point, fields do not have a property called AggregateType
'Lets create one
Set prop = qdf.fields("Merged").CreateProperty("AggregateType", dbInteger, 0) ' make property
qdf.fields("Merged").Properties.Append prop ' assign it to field
Set prop = qdf.fields("Imported").CreateProperty("AggregateType", dbInteger, 0)
qdf.fields("Imported").Properties.Append prop
Set prop = qdf.fields("Count").CreateProperty("AggregateType", dbInteger, 0)
qdf.fields("Count").Properties.Append prop
'currently, the QueryDef doesn't have a TotalsRow property.
'Lets make one for it
qdf.Properties.Append qdf.CreateProperty("TotalsRow", dbBoolean, True)
qdf.fields.Refresh ' probably not needed
3. 但针对子窗体 数据表视图 却无法 控制，只能
先在设计视图，先手工打开显示合计行。 me.recordset
再用代码控制 每个字段的合计方式： me.controls("fieldname").P
in design mode of the form.
me.formname.form.recordset
for this part open the form in design mode and enable the total row using the method msdn and microsoft says to do.
now if you want to set the aggreation at run time. you can.
on form open event..
me.controls("fieldname").P
but you can use -1 to turn it off.
1 give you the next one in the list.
any got this to open a form and apply sum to the fields that I wanted it to be applied.
