By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,127 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

Running Sum

P: 1
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:

Expand|Select|Wrap|Line Numbers
  1.  Option Explicit 
  2. Public Function frmRunSum(curForm As Form, idName As String, _
  3. idValue, sumField As String)
  4. '***********************************************************
  5. '* curForm - Always 'Me' for easy setting of form object. *
  6. '* idName - Unique fieldname (usually primarykey). *
  7. '* idValue - Value of idName. *
  8. '* sumField - The name of the field to runsum *
  9. '***********************************************************
  10. Dim rst As DAO.Recordset, subSum
  11.  
  12. Set rst = curForm.RecordsetClone
  13.  
  14. 'Find the current record via proper syntax for Data Type.
  15. Select Case rst.Fields(idName).Type
  16. Case dbLong, dbInteger, dbCurrency, _
  17. dbSingle, dbDouble, dbByte 'Numeric Data Type
  18. rst.FindFirst "[" & idName & "] = " & idValue
  19. Case dbDate 'Date Data Type
  20. rst.FindFirst "[" & idName & "] = #" & idValue & "#"
  21. Case dbText 'Text Data Type
  22. rst.FindFirst "[" & idName & "] = '" & idValue & "'"
  23. Case Else
  24. rst.MovePrevious 'Set BOF!
  25. End Select
  26.  
  27. 'Running Sum (subTotal) for each record occurs here.
  28. Do Until rst.BOF
  29. subSum = subSum + Nz(rst(sumField), 0)
  30. rst.MovePrevious
  31. Loop
  32.  
  33. frmRunSum = subSum
  34.  
  35. Set rst = Nothing
  36.  
  37. End Function
  38.  
I then added the following into the Form_frmCardBankPymnts Class Object in the VBA window:

Expand|Select|Wrap|Line Numbers
  1.  Public Function frmCardBankPymnts() 
  2.  
  3. '*******************************************************************
  4. '* idName - Unique field name. *
  5. '* sumField - The name of the field to runsum *
  6. '*******************************************************************
  7.  
  8. If Not IsNull(Me!Balance) Then 'Skip New Record!
  9. frmCardBankPymnts = frmRunSum(Me, "Balance", Me!Balance, "Figure")
  10. End If
  11.  
  12. End Function
  13.  
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
Jul 13 '07 #1
Share this Question
Share on Google+
1 Reply


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

Post your reply

Sign in to post your reply or Sign up for a free account.