I've been looking for a solution too. Could not find one either except writing a query based on the crosstab and then summing that one and adding in to the bottom in a union query. Since I try to do all SQL statements from inside a form (more manageable to deploy) I do not like this approach: writing or refilling a Querydef/view from code etc.
If you display the results in a subform on your form, you might do the following:
below the subform, and another subform short enough to hold only 1
record.
Bind the controls in the form to a function as follows:
control1 = fnADOSum(yourCrosstabfield1, yourCrosstabSQL)
Public Function fnADOSum(fldName As String, strInputSQL As String) As Double
On Error GoTo ERRHANDLER
Dim RS1 As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim StrSQL As String
Dim dblRunTot As Double
Set RS1 = New ADODB.Recordset
RS1.CursorLocation = adUseServer
Set cnn = CurrentProject.Connection
dblRunTot = 0
With RS1
.Open strInputSQL, cnn, adOpenForwardOnly, adLockReadOnly
If Not .EOF And Not .BOF Then
.MoveFirst
Do Until .EOF
dblRunTot = dblRunTot + Nz(.Fields(fldName).Value, 0)
.MoveNext
Loop
End If
.Close
End With
fnADOSum = dblRunTot
'CLEAN UP:
cnn.Close
Set RS1 = Nothing
Set cnn = Nothing
EXITHANDLER:
Exit Function
ERRHANDLER:
'' your own error handling proc
'' LogError err.Number, err.Description
End Function
