First Published 30 Oct 2023                 Last Updated 1 Nov 2023


This article is expands on a discussion from 2019 at Access World Forums Using Totals row in a table via VBA with particular reference to my code in post #12.

Access tables have a boolean property called TotalsRow (with no space) which is True or False (default).

The Totals row feature provides a simple way of providing aggregate values (Sum/Count/Average/Maximum/Minimum/Standard Deviation/Variance) in a table/query or datasheet form with no code required.

To do so, open the datasheet then click Totals in the Home ribbon



You can then click the dropdown in any number or text field and select a totals row type


NOTE:
1.   For text fields , the only options are None and Count.

2.   There is little point adding a Totals Row to a table or query as end users should never work with either of these.



Using Code:

Whilst the Totals row is easily added/removed using the ribbon, it is also possible to do this in a table or query using VBA code:

To add a totals row in code, use one of the following methods:

a)   Use a command bar

Application.CommandBars.ExecuteMso "RecordsTotals"

      This toggles the TotalsRow property on/off

b)   Add a totals row by setting the TotalsRow property to True

CurrentDb.TableDefs("YourTableName").Properties("TotalsRow") = True

      Reset to False to remove the Totals row

      You can then use code similar to that below to populate the totals row for several fields

Sub PopulateTotalsRowTable()

      Dim db As DAO.Database
      Set db = CurrentDb

      'Table must be closed and reopened to see any changes
      DoCmd.Close acTable, "Table1", acSaveYes

      With db.TableDefs("Table1")

            'Add the totals row
            .Properties("TotalsRow") = True

            'number/currency/boolean fields
            .Fields("Payment1").Properties("AggregateType") = 0       'sum
            .Fields("Income1").Properties("AggregateType") = 1       'average
            .Fields("Income2").Properties("AggregateType") = 2       'count
            .Fields("Field1").Properties("AggregateType") = 3       'maximum
            .Fields("Other").Properties("AggregateType") = 4       'minimum
            .Fields("Field2").Properties("AggregateType") = 5       'standard deviation
            .Fields("Field3").Properties("AggregateType") = 6       'variance

            .Fields("NField").Properties("AggregateType") = -1       'none

      'text fields
            .Fields("Active").Properties("AggregateType") = 2       'count

      End With

      'reopen table
      DoCmd.OpenTable "Table1"

End Sub

      NOTE:
      1.   I'm not sure whether -1 should be used to set the Totals as None ...but it works!

      2.   Unfortunately, there is no way of stating what the value means Count/Sum/Average etc ... you have to click the dropdown to check

      3.   Click this link for More information about standard deviation and variance

c)   Remove a totals row and reset all aggregate types to None

Sub ClearTotalsRowTable()

      Dim db As DAO.Database, i As Integer, strField As String
      Set db = CurrentDb

      'Table must be closed and reopened to see these changes
      DoCmd.Close acTable, "Table1", acSaveYes

      With db.TableDefs("Table1")

            'set the Totals row false
            .Properties("TotalsRow") = False

            'also clear all field aggregate types
            For i = 0 To .Fields.Count - 1
                  strField = .Fields(i).Name
                  .Fields(strField).Properties("AggregateType") = -1       'none
            Next

      End With

      'reopen table
      DoCmd.OpenTable "Table1"

End Sub

      NOTE: Similar code works for queries. Just replace TableDefs with QueryDefs (see example database attached)

d)   Add/Remove Totals row & aggregate types in a datasheet form

      The above code does not work in datasheet forms as these do not have a Totals Row property & it cannot be set in code.
      Instead you could set the TotalsRow property of the form recordset to True/False as that will be based on a table or query.

      Normally you would set/clear the totals row manually when designing the form.
      However, similar code can be used in the form itself to achieve the same results.

      One way of doing this is to use a split form with just the datasheet showing and three buttons in the form header.


      Toggle the totals row on and assign the aggregate types using a button click event (or in Form_Load).

Private Sub cmdAdd_Click()

      'This code could be moved to the Form_Load event

      'toggle totals row on
      Application.CommandBars.ExecuteMso "RecordsTotals"

      With Me
            'number/currency/boolean fields
            .Population.Properties("AggregateType") = 0       'sum
            .Households.Properties("AggregateType") = 1       'average
            .Postcodes.Properties("AggregateType") = 2       'count
            .Latitude.Properties("AggregateType") = 3       'maximum
            .Longitude.Properties("AggregateType") = 4       'minimum
            .ActivePostcodes.Properties("AggregateType") = 5       'standard deviation
            .NonGeographicPostcodes.Properties("AggregateType") = 6       'variance
            .InUse.Properties("AggregateType") = 6       'variance

            'text fields
            .AreaCovered.Properties("AggregateType") = 2       'count
            .Districts.Properties("AggregateType") = -1       'none
      End With

      cmdAdd.Enabled = False
      cmdRemove.Enabled = True

End Sub

      Toggle the totals row off again and clear the aggregate types using another button click event (or in Form_Close).

Private Sub cmdRemove_Click()

      'This code could be moved to the Form_Close event

      'toggle totals row off
      Application.CommandBars.ExecuteMso "RecordsTotals"

      With Me
            'set all number/currency/boolean fields to -1 (None)
            .Population.Properties("AggregateType") = -1
            .Households.Properties("AggregateType") = -1
           .Postcodes.Properties("AggregateType") = -1
           .Latitude.Properties("AggregateType") = -1
           .Longitude.Properties("AggregateType") = -1
           .ActivePostcodes.Properties("AggregateType") = -1
           .NonGeographicPostcodes.Properties("AggregateType") = -1
           .InUse.Properties("AggregateType") = -1

           'set all text fields to -1 (None)
           .AreaCovered.Properties("AggregateType") = -1
           .Districts.Properties("AggregateType") = -1

      End With

      cmdAdd.Enabled = True
      cmdRemove.Enabled = False

End Sub

      You can also use the Toggle button to show / hide the Totals row without altering the aggregate type values

Private Sub cmdToggle_Click()

      'toggle the totals row on/off - don't change the aggregate type values
      Application.CommandBars.ExecuteMso "RecordsTotals"

      'toggle the Add/Remove buttons enabled state
      Me.cmdAdd.Enabled = Not Me.cmdAdd.Enabled
      Me.cmdRemove.Enabled = Not Me.cmdAdd.Enabled

End Sub

      Two other events are used to ensure the logic doesn’t get reversed:

Private Sub cmdClose_Click()
      If cmdRemove.Enabled = True Then cmdRemove_Click
      DoCmd.Close
End Sub

'==========================================

Private Sub Form_Load()
      cmdAdd.Enabled = True
      cmdRemove.Enabled = False
End Sub

Further Info

When the totals row feature was implemented in Access 2007, it was expected that it would be done totally with the user interface.
As a result, none of the above code has been documented anywhere by Microsoft.

I deduced most of the code by trial and error after initially going down a 'rabbit hole' with the acAggregateType constants that I found in the VBE:



The Help documentation for those constants is available at: AcAggregateType enumeration (Access)



There were two significant issues with that information
1.   Two of the totals row option were not listed: Standard deviation and Variance
2.   All of the values were shifted by 1 leading to incorrect outcomes

In turns out that the reason for those differences is that the acAggregateType values apply to ChartValues . . . NOT the TotalsRow property.
See the Help article: ChartValues.AggregateType property (Access)

Clearer documentation would be very helpful!



Download

The example database includes all the above table / form code and additional procedures for use with queries. The form code will also work in ACCDE files.

Click to download:       Totals Row VBA v1.2                  Approx 0.55 MB  (zipped)

Download the zip file and unblock it.
For more details, see my article: Unblock downloaded files by removing the Mark of the Web

Unzip and save the ACCDB file to a trusted location.



Version History

Version      Date              Notes
v1.0           29/10/2023     Initial release - table/query code only
v1.1           30/10/2023     Added code to handle totals row in forms
v1.2           31/10/2023     Added toggle button to form



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 1 Nov 2023

Return to Code Samples Page

Return to Top