手工显示合计行（列汇总） 我们都知道如何操作，可参考：
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 0End FunctionSub 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 = NothingEnd 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 1qrydef.Fields(2).Properties("AggregateType").Value = 1 'add the Average Total on field 2qrydef.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 With
Set 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
.propertie s("Totalro w") = True 只会影响窗体绑定的表或查询的的汇总方式，而不是窗体数据视图本身。（即如果窗体绑定的查询设置了合计，则msgbox me.recordset .propertie s("Totalro w") 结果为True. 与窗体的合计行显示没有关系） 再用代码控制 每个字段的合计方式： me.controls("fieldname").P
roperties( "Aggregate Type") = 0 in design mode of the form. me.formname.form.recordset
.propertie s("Totalro w") = Truefor 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 roperties( "Aggregate Type") = 0 gives you Sumbut 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.