VBA to add Totals to subform

[bodders24]

I have built a procedure to dynamically rebuild a subform and underlying table based on a crosstab query. This is so that it can show various combinations of categories (rows) and monthly totals (columns). I would like to have a total row appearing on the form and to use VBA to achieve this. The subform is based on a select query from the calculated table.

I have read several posts, and I think this is the code to do so:

    Set qdf = dbs.QueryDefs("qselBudgetXT")
    qdf.Properties("TotalsRow") = True
     qdf.Fields(2).Properties("AggregateType") = 0
     qdf.Fields(3).Properties("AggregateType") = 0
     qdf.Fields(4).Properties("AggregateType") = 0
     qdf.Fields(5).Properties("AggregateType") = 0
     qdf.Close

However, the subform shows a Totals row, but no actual totals in the cells in that row. I can add them manually but that defeats the object.

Does anyone have any thoughts or suggestions. All help gratefully received.

Thanks

Bodders

 

[Gasman]

Aren't you setting them to zero?

 

[bodders24]

Aren't you setting them to zero?

In other posts I have read using the AggregateType of 0 should produce a Sum total

 

[Gasman]

Never used them, but just try setting to any value as a debug option?

How to display totals row using VBA?

is there a way to add sum of a column using VBA? I know I could use 'Application.CommandBars.ExecuteMso "RecordsTotals" to add the "Total" row, but I can't seem to find the way to add the sum of a ...

stackoverflow.com

How to create an Access crosstab query with totals for the columns AND the rows?

I want my query result to look like this: Person1 Person2 Person3 Person4 Total Status1 2 4 7 3 16 Status2 0 1 0 3 4 Sta...

stackoverflow.com

 

[CJ_London]

my guess is you have turned off warnings. The totals row property doesn’t exist until you create it. See bottom of this link

Access Database turn on aggregate type through VBA

Can anyone review my code here and see where I did wrong. My objective is to turn on the Aggregate Type in the Datasheetview after creating the query. So far Function LoopQuery(ByVal tempProj As

stackoverflow.com

 

[bodders24]

Never used them, but just try setting to any value as a debug option?

How to display totals row using VBA?

is there a way to add sum of a column using VBA? I know I could use 'Application.CommandBars.ExecuteMso "RecordsTotals" to add the "Total" row, but I can't seem to find the way to add the sum of a ...

stackoverflow.com

How to create an Access crosstab query with totals for the columns AND the rows?

I want my query result to look like this: Person1 Person2 Person3 Person4 Total Status1 2 4 7 3 16 Status2 0 1 0 3 4 Sta...

stackoverflow.com

Interestingly the query opened on its own has the Total row with the correct values. Only on the subform - opened separately - it does not. Strange

 

[isladogs]

I think you need the acAggregateType enumeration with one of the following values

See AcAggregateType enumeration (Access) | Microsoft Learn

 

[CJ_London]

Only on the subform

do you mean the subform, or are you using the query as the source object to the subform control

 

[bodders24]

Here's the query opened on its own with subtotals showing:

And here is the subform with the above query as its Recordsource (no totals showing):

I cannot work out why this should be.

 

[Gasman]

Look at post #7?

 

[CJ_London]

also post #8

 

[bodders24]

Look at post #7?

Changing

qdf.Fields(5).Properties("AggregateType") = 0

to

qdf.Fields(5).Properties("AggregateType") = acAggregateSum

actually produces the wrong answer in the query. It gives the average.

 

[CJ_London]

what about using 1 instead of 0?

 

[Gasman]

Changing

qdf.Fields(5).Properties("AggregateType") = 0

to

qdf.Fields(5).Properties("AggregateType") = acAggregateSum

actually produces the wrong answer in the query. It gives the average.

So try them all.
Not the first time the MS help could be wrong.

 

[bodders24]

my guess is you have turned off warnings. The totals row property doesn’t exist until you create it. See bottom of this link

Access Database turn on aggregate type through VBA

Can anyone review my code here and see where I did wrong. My objective is to turn on the Aggregate Type in the Datasheetview after creating the query. So far Function LoopQuery(ByVal tempProj As

stackoverflow.com

Warnings are definitely on

 

[CJ_London]

how about a comment on the link I provided? plus post #8 - sounds like you are using a form (per post 9), not the query. To use the query the subform sourceobject would be

query.nameofquery

your code is modifying the querydef, not the form

 

[bodders24]

Thanks for the responses. Having tried everything suggested above, here is the current situation.

The query which is the SourceObject for the datasheet subform produces a totals row and individual total rows exactly as it should, using AggregateType = 0.

The problem is the subform. I can add the AggregateType property to each control, but it does not show when the form is displayed. It also does not allow me to set a TotalsRow property for the form.

Opening the subform separately allows me to manually create the totals for each cell in the Totals row, but the dynamic VBA process deletes these controls when it is rebuilt, and the totals are lost, despite the VBA recreating them:

        If HasProperty(ctl, "AggregateType") = False Then
            Set obj = ctl
            obj.Properties.Append dbs.CreateProperty("AggregateType", dbLong, acAggregateSum)
        Else
            ctl.Properties("AggregateType") = acAggregateSum
        End If

This code produces an error:

If HasProperty(frm, "TotalsRow") = False Then
    Set obj = frm
    obj.Properties.Append dbs.CreateProperty("TotalsRow", dbBoolean, True)
End If

 

[CJ_London]

Having tried everything suggested above

so what happened when you used the query as the subform control source object?

works for me

 

[bodders24]

so what happened when you used the query as the subform control source object?

This is what I see with the query as the subform SourceObject - a Total row but no totals:

 

[CJ_London]

you haven't shown your full code, perhaps you are not saving the query def, or not refreshing the subform control