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

Blueskies

Registered User.
Local time
Today, 07:57
Joined
Oct 23, 2009
Messages
69
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

AWF VIP
Local time
Today, 07:57
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 07:57
Joined
Oct 23, 2009
Messages
69
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:

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

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

AWF VIP
Local time
Today, 07:57
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 07:57
Joined
Oct 23, 2009
Messages
69
Yeah - underscore would be best.

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

isladogs

MVP / VIP
Local time
Today, 07:57
Joined
Jan 14, 2017
Messages
18,186
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

DSSplitForm2.png


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

Users who are viewing this thread

Top Bottom