Solved Totals row not displayed on subform but works in query

[Kayleigh]

Hi there

I have encountered an issue which seems to be quite common but can't find a resolution.

I have a subform which is a datasheet. The recordsource is a query. I am trying to add a totals row - it works as a query but will not display in the form. Any ideas why?

This is the base query (qryAccByStaffFilter):

SELECT tblTransaction.*, tblTransactionItem.*, Format([fldtdatetime],"mmddyyyyhhnnss") AS cfDateID, lkptblTransEvent.fldEventName
FROM tblTransaction INNER JOIN (tblAccounts INNER JOIN (lkptblTransEvent INNER JOIN tblTransactionItem ON lkptblTransEvent.fldTransEventID = tblTransactionItem.fldTEventID) ON tblAccounts.fldAccountID = tblTransactionItem.fldAccountID) ON tblTransaction.fldTransactionID = tblTransactionItem.fldTTransactionID
WHERE (((tblAccounts.fldAccountTypeID)=6) AND ((tblAccounts.fldContactID)=[forms]![frmStaff]![fldContactID]))
ORDER BY tblTransaction.fldTDateTime;

And this is the recordsource query:

SELECT qryAccByStaffFilter.fldTransactionID, Format([fldTDateTime],"dd/mm/yyyy") AS cfDate, [fldTDesc] & IIf(isnothing([fldtnote]),""," - " & [fldtnote]) AS cfNotes, qryAccByStaffFilter.fldEventName, qryAccByStaffFilter.fldTValue, CCur(DSum("fldtvalue","qryaccbystafffilter","val([cfdateid])  <= " & Val([cfdateid]))) AS runTot, qryAccByStaffFilter.fldTDateTime
FROM qryAccByStaffFilter
ORDER BY qryAccByStaffFilter.fldTDateTime DESC;

 

[Pat Hartman]

Did you try adding the totals row in the subform? It will NOT carry over from a query.

 

[isladogs]

As Pat mentioned, the Totals row has to be applied to each object separately.
Here's a subform with a Totals row

 

[MajP]

Totals row has to be applied to each object separately

Can you explain how that is done? I cannot figure out where to do this.

 

[isladogs]

With the object (table/query/form) open, click Totals on the Home tab

 

[Kayleigh]

Yes I have tried that.

My latest discovery is it works when form is NOT pop out but when i select pop out the 'totals' are greyed out. But now it works because I already set the totals when form was not pop out. Weird work around!

 

[Pat Hartman]

@MajP It also is only available for forms in DS view.

 

[MajP]

Thanks. That is kind of strange how that works, not in design view.

 

[isladogs]

The Totals row isn't a design feature
It is a change to the application interface so has to be applied in form view (or the equivalent for tables & queries)

As @Kayleigh has observed. Totals are disabled on the ribbon for popup forms.
I presume this is because popups are not part of the application interface - they are displayed independently.
However, if added before a form is made a popup, the totals feature still works and each column total type can be edited

 

[Pat Hartman]

These totals are something of a kludge. I frequently have trouble with them just disappearing. So, I don't use them in production applications. I stick with my old method of controls in the form or subform's footer to display the totals. It's not like I make enough design changes to make this method a problem. And the old method works on continuous as well as ds view subforms. The difference is that since you can't see the DS view subform's footer, you have to have controls on the main form to pull the data from the subform's footer.