Using Totals row in a table via VBA (1 Viewer)

Blueskies · Apr 17, 2019

Hi

I've always found the Totals button on the Home toolbar (Access 2013) to be useful to produce this sort of thing:

Is there a way to add this row and set it to 'sum' for a given table from VBA?

Cheers!

essaytee · Apr 17, 2019

There are many ways within VBA to count things.

For starters, have a look at the DCount() function. Or the DSum() function

Blueskies · Apr 17, 2019

Hi Steve

Thanks for your reply. I'm familiar with the other methods, I just thought that the Total line was nice to keep on the table - that way I dont need to create a query, report or whatever.

I could add a line with total on it, but I like the way the button makes it 'live' so you can filter out some of the records and it updates.

essaytee · Apr 17, 2019

Blueskies said:
Hi Steve

Thanks for your reply. I'm familiar with the other methods, I just thought that the Total line was nice to keep on the table - that way I dont need to create a query, report or whatever.

I could add a line with total on it, but I like the way the button makes it 'live' so you can filter out some of the records and it updates.

Sorry, I misunderstood your question.

I'm not aware of a feature or function to automatically do this, that's not to say it's not possible.

Depending upon your needs, that is, if you are the only person using your database, then working directly with Tables is ok. If others are using your database, then it's wise to use forms and queries to dispaly the data and keep prying eyes away from the tables.

isladogs · Apr 17, 2019

Access tables have a property called TotalsRow (with no space) which is either True or False.
Some properties are read only and my suspicion is that this is one such property.
I've never heard of that property being set using code and I suspect you cannot do so.

In any case, there's little point. If you add a Totals Row and then save your table, it will reappear whenever you open the table.

One more thing to stress is that end users should NEVER work with tables. Forms should always be used
EDIT - sorry - Steve had already written that point!

Blueskies · Apr 17, 2019

Hi both

Thanks for replies - sounds like it's not do-able, (though if you can save the Totals row when you close the table then you would have thought it should be possible somehow). I'll have a look at the TotalsRow anyway.

This was just for my benefit - I create the table itself from VBA (it's just a quick list of the total records in each table in a database for me to refer to), so I'll just add the totals each time from the button.

It's not a biggie by any means - just curious as to whether I could save myself the trouble of having to click a button!

isladogs · Apr 17, 2019

Well you only need to click the button once for each table!

Blueskies · Apr 17, 2019

Yeah, just curious if it could be done really!

theDBguy · Apr 17, 2019

Blueskies said:
Yeah, just curious if it could be done really!

Hi. Pardon me for jumping in... I seem to recall having a discussion about this before where we concluded it was indeed possible, but I can't seem to find the thread to remind me how we did it. So, I just had to do a little experiment and came up with the following expression instead:

Code:

Application.CommandBars.ExecuteMso "RecordsTotals"

It toggles the row on and off. I'm not sure yet how to do the "sum" part. Hope it helps...

theDBguy · Apr 17, 2019

theDBguy said:
I'm not sure yet how to do the "sum" part.

Okay, here's how I was able to set the field to have the Sum in the Totals Row.

Code:

FieldName.Properties("AggregateType")=0

Cronk · Apr 17, 2019

A field's aggregateType can be set to some other cases eg Count, Average. Look up acAggregateType in the Object browser window for all options.

BTW, I've never seen need to use it. My users never get to see tables and any total I'd want is normally on a filtered subset of records, or after grouping. If users want totals, they can have it in an Access report or by my adding =Sum() to a column after the data has been exported to Excel.

isladogs · Apr 17, 2019

I've also never found a real use for this feature but did a bit more experimentation following DBG's posts

Returning to my TotalsRow comment in post #5, this alternative code adds the total row:

Code:

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

Set = False to remove the TotalsRow again

Also the other totals properties can be assigned with AggregateType value corresponding to the list position. For example

Code:

Sub PopulateTotalsRow()

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

'number/currency fields
CurrentDb.TableDefs("table1").Fields("Payment1").Properties("AggregateType") = 0 'sum
CurrentDb.TableDefs("table1").Fields("Income1").Properties("AggregateType") = 1 'average
CurrentDb.TableDefs("table1").Fields("Income2").Properties("AggregateType") = 2 'count
CurrentDb.TableDefs("table1").Fields("Field1").Properties("AggregateType") = 3 'maximum
CurrentDb.TableDefs("table1").Fields("Other").Properties("AggregateType") = 4 'minimum

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

'text / boolean fields
CurrentDb.TableDefs("table1").Fields("Active").Properties("AggregateType") = 2 'count
End Sub

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

Blueskies · Apr 18, 2019

Thanks for the replies guys - I'll give this a try.

Hope you enjoy the weekend!

theDBguy · Apr 18, 2019

Blueskies said:
Thanks for the replies guys - I'll give this a try.

Hope you enjoy the weekend!

Happy Easter!

isladogs · Apr 18, 2019

Cronk said:
BTW, I've never seen need to use it. My users never get to see tables and any total I'd want is normally on a filtered subset of records, or after grouping. If users want totals, they can have it in an Access report or by my adding =Sum() to a column after the data has been exported to Excel.

@Cronk
For info, you can also apply a totals row to queries and perhaps more usefully, to datasheet forms. The latter is the only place where I've ever made use of it in my apps ... but hardly ever as I rarely use datasheet forms anyway.

If you filter the table/query/form, the Totals row is automatically updated

What I really don't like is that there is no way of stating what the value means Count/Sum/Average etc ... you have to click the dropdown to check

Blueskies · Apr 25, 2019

Hi guys

I'm back on this after a little break - hope you all enjoyed Easter.

Following posts above and some googling I've now got the below - table name is '* Table Counts' and the field I want to total is called 'Count':

Code:

Dim prop As DAO.Property
     
Set prop = CurrentDb.TableDefs("* Table Counts").Fields("Count").CreateProperty("AggregateType", dbInteger, 0)
    
CurrentDb.TableDefs("* Table Counts").Fields("Count").Properties.Append prop
            
CurrentDb.TableDefs("* Table Counts").Properties.Append CurrentDb.TableDefs("* Table Counts").CreateProperty("TotalsRow", dbBoolean, True)
        
CurrentDb.TableDefs("* Table Counts").Fields.Refresh

DoCmd.OpenTable "* Table counts", acViewNormal

I get no errors from this, and I do see the Totals row, but it's still not set to 'Sum'

I think I must be almost there! - can anyone help me jump the last hedge?

Cheers!

isladogs · Apr 25, 2019

Did you see my code in post #12?
You shouldn't need to create the property as its built in to Access

Blueskies · Apr 25, 2019

Hi

I did see the post, but when I tried it without creating the property first I got error messages about it not existing?

The below gives error 3270 Property Not Found

Code:

CurrentDb.TableDefs("* Table Counts").Properties("TotalsRow") = True
  
CurrentDb.TableDefs("* Table Counts").Fields("Count").Properties("AggregateType") = 0

Gasman · Apr 25, 2019

I tried your code and it complained of item already in collection.

Tried Colin's code and it worked first time.?

isladogs · Apr 25, 2019

Fair enough but now you've created the property, you should be able to use the AggregateType code from my post which definitely works ...assuming of course that you have a number or currency field.

EDIT
However the problem is likely to be your table name which should only contain letters & numbers -no special characters (apart from underscore) and no spaces.
Change "* Table Counts" to TableCounts and it should work.
As written, all it uses is the * part which is a wildcard. Surprised you didn't get an error
It MIGHT work if you enclose in [] but you will have endless problems with that name

Also Count is a reserved word in Access. Suggest you change it to save other problems

Using Totals row in a table via VBA (1 Viewer)

Registered User.

Need a good one-liner.

Registered User.

Need a good one-liner.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

I’m here to help

I’m here to help

Registered User.

MVP / VIP

Registered User.

I’m here to help

MVP / VIP

Registered User.

MVP / VIP

Registered User.

Enthusiastic Amateur

MVP / VIP

Similar threads

Users who are viewing this thread