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

[Blueskies]

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]

There are many ways within VBA to count things.

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

 

[Blueskies]

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]

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]

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]

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]

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

 

[Blueskies]

Yeah, just curious if it could be done really!

 

[theDBguy]

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:

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]

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.

FieldName.Properties("AggregateType")=0

 

[Cronk]

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]

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:

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

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]

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


Hope you enjoy the weekend!

 

[theDBguy]

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


Hope you enjoy the weekend!

Happy Easter!

 

[isladogs]

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]

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':

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]

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

 

[Blueskies]

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

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

 

[Gasman]

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

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

 

[isladogs]

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