GEORGEKEAY
Programmer
- Oct 12, 2013
- 1
Good morning,
I have problem to take the running sum in the form, i am working with Module, like that
Option Compare Database
Option Explicit
Public Function frmRunSum(frm As Form, pkName As String, sumName As String)
Dim rst As DAO.Recordset, fld As DAO.Field, subTotal
Set rst = frm.RecordsetClone
Set fld = rst(sumName)
'Set starting point.
rst.FindFirst "[" & pkName & "] = " & frm(pkName)
'Running Sum (subTotal) for each record group occurs here.
'After the starting point is set, we sum backwards to record 1.
If Not rst.BOF Then
Do Until rst.BOF
subTotal = subTotal + Nz(fld, 0)
rst.MovePrevious
Loop
Else
subTotal = 0
End If
frmRunSum = subTotal
Set fld = Nothing
Set rst = Nothing
End Function
Private Function SubSum()
'*************************************************************
'* pkName - Existing unique fieldname (usually primarykey) *
'* sumName - Name of the field to runsum *
'*************************************************************
If Trim(Me!pkName & "") <> "" Then 'Skip New Record!
SubSum = frmRunSum(Me, "pkName", "sumName")
End If
End Function
THEN I CALL IN THE FORM ON TEXTBOX SubSum() , like that Subsum([[DEPIT]) BUT IS NOT WORKING
THANKS FOR ANY ADVANCE
I have problem to take the running sum in the form, i am working with Module, like that
Option Compare Database
Option Explicit
Public Function frmRunSum(frm As Form, pkName As String, sumName As String)
Dim rst As DAO.Recordset, fld As DAO.Field, subTotal
Set rst = frm.RecordsetClone
Set fld = rst(sumName)
'Set starting point.
rst.FindFirst "[" & pkName & "] = " & frm(pkName)
'Running Sum (subTotal) for each record group occurs here.
'After the starting point is set, we sum backwards to record 1.
If Not rst.BOF Then
Do Until rst.BOF
subTotal = subTotal + Nz(fld, 0)
rst.MovePrevious
Loop
Else
subTotal = 0
End If
frmRunSum = subTotal
Set fld = Nothing
Set rst = Nothing
End Function
Private Function SubSum()
'*************************************************************
'* pkName - Existing unique fieldname (usually primarykey) *
'* sumName - Name of the field to runsum *
'*************************************************************
If Trim(Me!pkName & "") <> "" Then 'Skip New Record!
SubSum = frmRunSum(Me, "pkName", "sumName")
End If
End Function
THEN I CALL IN THE FORM ON TEXTBOX SubSum() , like that Subsum([[DEPIT]) BUT IS NOT WORKING
THANKS FOR ANY ADVANCE