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

[Blueskies]

Thanks for posts guys.


I'll have a check through and see if I can find a problem. The 'Count' field is Long Integer, so no problem there, and I can click the Sum on manually.

I can't quite understand why the property is there by default for you guys, but not for me! - I'll do some more testing.....


EDIT - thanks for advice on table/field names. I'll give that a go for starters.

 

[Minty]

I never cease to be amazed at what Access will let you set as a field or table name.

* Table Counts


Has to go against almost every grain of what should be allowable ...

Edit - I see Colin beat me to it, the kettle had boiled!

 

[Blueskies]

LOL!

Prefixing with a * on a table name is a bit of an old habit I've got as a way of bringing it to the top when tables are alpha-sorted.

It does cause me issues sometimes when I export to a excel file....

Anyhow, I've tried the code again on a new table and it now works:

CurrentDb.TableDefs("Table Counts").Properties("TotalsRow") = True    

CurrentDb.TableDefs("Table Counts").Fields("Countme").Properties("AggregateType") = 0

ie the property was there to start with.

I believe the issue is how the table was created in the first place - I took a bit of a short cut and created it from a bit of sql:

DoCmd.RunSQL "SELECT '' AS [Table Name], 0 AS [Count] INTO [* Table Counts];"
DoCmd.RunSQL "DELETE [* Table Counts].* FROM [* Table Counts];"

another bad habit! - I'll sort it out, but the original query is answered, so all good!


Thanks to everyone who posted - much appreciated.

 

[Minty]

Use an underscore as the first character or simply aaaa_Mytemptable

We've all been there with temp things - I use my initials at the start of "interesting" queries that are normally only there to fix someones **** up. It makes them easy to find in a large database.

 

[Blueskies]

Yeah - underscore would be best.

Just a case of stopping myself reaching for the shift-8 key combination!

 

[isladogs]

I was reminded about this old thread by a fellow Access developer who asked whether the same code could be used in a datasheet form / subform.

As written, the above code in post #12 works for tables & is easily adapted for queries.
However it does not work for forms & the totals row isn't propagated to forms from the table/query record source as I originally believed.

It took a bit more effort than I expected but I was able to extend the idea to forms. This is a datasheet only split form so I can include buttons

For more info, code & an example database, see my article:

Manage Totals row using VBA

This article discusses how totals rows can be added to /edited in /removed from Access tables, queries and forms using VBA.

isladogs.co.uk