Running Sum

[BrianMiller]

I have searched for an answer but have failed to come up with anything that seems to work.

I am trying to set up an Access 2003 database for home accounts. I want to create a running sum (a balance based on what has been spent or paid in).

I have a table called tblCardBankPymnts. In that I have a Figure column for what transaction has been done. I also have a Balance column but think I should not have this now.

I then have a form to fill this in and I think it is on this form I need to have the Running Sum in the Balance text box.

I came across the following VBA code and added the following into a new module called modRunSum:

 Option Explicit 
Public Function frmRunSum(curForm As Form, idName As String, _
idValue, sumField As String)
'***********************************************************
'* curForm - Always 'Me' for easy setting of form object. *
'* idName - Unique fieldname (usually primarykey). *
'* idValue - Value of idName. *
'* sumField - The name of the field to runsum *
'***********************************************************
Dim rst As DAO.Recordset, subSum
 
Set rst = curForm.RecordsetClone
 
'Find the current record via proper syntax for Data Type.
Select Case rst.Fields(idName).Type
Case dbLong, dbInteger, dbCurrency, _
dbSingle, dbDouble, dbByte 'Numeric Data Type
rst.FindFirst "[" & idName & "] = " & idValue
Case dbDate 'Date Data Type
rst.FindFirst "[" & idName & "] = #" & idValue & "#"
Case dbText 'Text Data Type
rst.FindFirst "[" & idName & "] = '" & idValue & "'"
Case Else
rst.MovePrevious 'Set BOF!
End Select
 
'Running Sum (subTotal) for each record occurs here.
Do Until rst.BOF
subSum = subSum + Nz(rst(sumField), 0)
rst.MovePrevious
Loop
 
frmRunSum = subSum
 
Set rst = Nothing
 
End Function
 

I then added the following into the Form_frmCardBankPymnts Class Object in the VBA window:

 Public Function frmCardBankPymnts() 
 
'*******************************************************************
'* idName - Unique field name. *
'* sumField - The name of the field to runsum *
'*******************************************************************
 
If Not IsNull(Me!Balance) Then 'Skip New Record!
frmCardBankPymnts = frmRunSum(Me, "Balance", Me!Balance, "Figure")
End If
 
End Function
 

On the frmCardBankPymnts form (used to enter the data into the tblCardBankPymnts table) in the Balance textbox Control Source box I then have: =frmCardBankPymnts()

I have a start figure in row one of the tblCardBankPymnts in the Figure column of 100 pounds and then in the next row a figure of 50 pounds. Yet, on the form it displays 100 pounds in each of the rows, not 150 as I would have expected.

Can anyone help please? I see in Access 2007 there is a Running Sum funtion, but in Access 2003 this does not exist.

Many thanks

Brian Miller

[AdrianJ1980]

It's easy to do in a report through the properties menu as per http://office.microsoft.com/en-us/ac...873881033.aspx