Full Version: Running Sum In Continuous Form
UtterAccess Forums > Microsoft® Access > Access Forms
ohiogto1969
I know the golden rule about searching for an answer prior to posting a question, I have searched all over and can not come up with a solution to my problem.
I have a continuous form and would like to add an unbound text box to reflect a running balance. My table structure is simple.
Date = date of transaction
tType = could be about anything that is being paid for or credited ex;lowes, chase cc, dinner, cash recieved
tDeposit = deposit amount for "tType"
tWithdrawal = withdrawal amount for deductions
tNotes = notes that reflect details for transaction
On each line in the continuous form I would like to see the running balance (detail section)
I am attaching the database in case it makes the question more clear.
Thanks in advance for any help you can provide,
John
orange999
How about in a report http://office.microsoft.com/en-us/access-h...P005187388.aspx
ohiogto1969
I would like to do it in the form but if I have no other options then I will go with the report. Thanks
Dave Elliott
Here is an example;
HTH
ohiogto1969
What version of access is this sample in? I can not open it with Access 2007. Thanks
ohiogto1969
I am getting unrecognized database format when trying to open it.
Dave Elliott
My apologies, 2010 accdb format. Then for example you have a textbox named RunBal
Of course substitute your names and PK. You can also use this in qry. RunSum( YourCriteriaHere,etc...
unBal = RunSum([Form],"RegID",[RegID],"Amount")
Save this code in a module and name it ModRunSum
Option Compare Database
CODE
Function RunSum(F As Form, KeyName As String, KeyValue, _
  FieldToSum As String)
'***********************************************************
' FUNCTION: RunSum()
' PURPOSE:  Compute a running sum on a form.
' PARAMETERS:
'    F        - The form containing the previous value to
'               retrieve.
'    KeyName  - The name of the form's unique key field.
'    KeyValue - The current record's key value.
'    FieldToSum - The name of the field in the previous
'                 record containing the value to retrieve.
' RETURNS:  A running sum of the field FieldToSum.
' EXAMPLE:  =RunSum(Form,"ID",[ID],"Amount")
'***********************************************************
   Dim rs As DAO.Recordset
   Dim result
   On Error GoTo Err_RunSum
   ' Get the form Recordset.
   Set rs = F.RecordsetClone
   ' Find the current record.
   Select Case rs.Fields(KeyName).Type
      ' Find using numeric data type key value?
      Case DB_INTEGER, DB_Long, DB_CURRENCY, _
         DB_SINGLE, DB_DOUBLE, DB_BYTE
         rs.FindFirst "[" & KeyName & "] = " & KeyValue
      ' Find using date data type key value?
      Case DB_DATE
         rs.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
      ' Find using text data type key value?
      Case DB_TEXT
         rs.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
      Case Else
         MsgBox "ERROR: Invalid key field data type!"
         GoTo Bye_RunSum
   End Select
   ' Compute the running sum.
   Do Until rs.BOF
      result = result + rs(FieldToSum)
      ' Move to the previous record.
      rs.MovePrevious
   Loop
Bye_RunSum:
   RunSum = result
   Exit Function
Err_RunSum:
   Resume Bye_RunSum
End Function
Peter Hibbs
Hi ohiogto,
nother option (which may work in your situation) is to do the calculation in the query and then just base the Continuous form on the query. The query must be sorted in the correct order for the running sum which you should have already (like an AutoNumber field, for example). Then you would create a query something like the screen shot below, the ID field is an AutoNumber field in this example and the Amount field is the source data. The RunTotal field shows the running total, you would use your own field names, of course.
Then, in a code module, you would enter the VBA code shown below -
CODE
Public Function RunSum(vStart As Long, vAmount As Currency) As Currency
Static vSum As Currency
    If vStart = 1 Then vSum = 0 'reset running sum for first record
    RunSum = vSum + vAmount     'return new sum
    vSum = RunSum               'and save new sum
End Function

The vStart variable must be a value which can be used to reset the running sum variable (vSum) to 0 each time the query is run. In this example, the first record in the list of records has the ID field set to 1 so when the first record in the list is passed to the function, the vSum variable is set to 0, after that the variable is used to calculate the next value. So you would need to have some value that you can use to do this reset operation.
Maybe you can adapt this example for your own project.
Peter Hibbs.
ohiogto1969
I am really trying to get this to work. Right now I am trying Dave's solution and I am getting back "#Name?" in the unbound text box.
My unbound text box is named "runBal" the control source is set to "=RunSum([frmTransactions],"transactionId",[transactionId],"tWithdrawalAmt")"
"frmTransactions" is the name of my continuous form.
"transactionId" is the primary key
"tWithdrawalAmt" is the dollar amount I am trying to run the sum on.
Thanks again for all of your help.
John
gemmathehusky
the reason running sums are awkward in a form, as opposed to a report, is because a report is ordered, but a form is not. you can resort a form, and then you need a way to regenerate the running sum.
That's why in a form it's easier just to work with an overall total
Dave Elliott
Okay, try this.
TH
ohiogto1969
Thank you all for your help. Thanks Dave for taking the time out to show me the solution. I should have been able to figure it out by your original post. Again Thanks
Dave Elliott
That's okay.Glad to help. Good Luck With Your Project.
"vertical-align:middle" emoid=":thumbup:" border="0" alt="thumbup.gif" />
mkamel
Thank you for this interesting topic
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.