Running balance on form

[Cristina]

Hi all,
I put this post before, and no answer, I search the forum but cant find and answer that I can use, as I need basic help.
I have a database tracking a budget. It is like a cash boom. I have deposit and make payments. Supplier, invoice# etc. I would like my form to show a running balance of my deposits and expenditure, I need this for tomorrow. Can someone p;ease help. I need basic help. Thanks

 

[burrina]

Hi, this is just one way. Create a new Module and name it ModRumSum
Paste this code into it and save.

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

Next: On for form where you want to display the running balance add this.

=RunSum([Form],"RegID",[RegID],"Amount")

Of course, change RegID to your primary key.
Also change Amount for the field you want to sum on.

hth

 

[shoji]

Cristina,

It is difficult to answer your question unless you tell us more about your table structure. Since you mentioned "Running balance", is this a list? If so, you should work on a totals query. Also, google for DSum function.

 

[Cristina]

Thank you both for answering. I tried the module and it worked well.

Much appreciated.

Cristina