Form - Running Net Value Field in a subform for all records (1 Viewer)

[NLR]

Hello,

Is there a way to add a field to a subform that preforms a net value calculation on all the records in that subform?

Fields:
Record ID ItemAmt PaidAmt Need a Net field here? (not part of form; description only)
1 500.00 100.00 400.00 500.00-100.00
2 500.00 50.00 350.00 400.00-50.00


The ItemAmt is referring to the main form and should be the same amt.
I need the Net field to act as a running balance.

Thank you in advance.
NLR

 

[NLR]

Sorry, here is a better example of what I need...
Example
Record ID ItemAmt PaidAmt Need a Net field here? (not part of form; description only)
1 $500.00 $100.00 $400.00 500.00-100.00
2 $500.00 $50.00 $350.00 400.00-50.00

 

[burrina]

Here is a module to perform that function. Import into a new module and save as ModRunSum

Option Compare Database

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

Example:

SomeControl =RunSum([Form],"PrimaryKey",[PrimaryKey],"FieldToSum")

HTH

 

[NLR]

burrina,
I'm not very good at using modules. Would you explain how I would use the code you've provided?
Thanks,
NLR

 

[burrina]

I thought I explained it good enough!
1. Import the code into a New module and save.
2. Use example code for field on your form using YOUR Primary Key and Field name.

HTH

 

[NLR]

Hi,
I'm sorry; but I don't know VB.
I've copied the code to a module and saved it. Changed all "KeyName" to the unique ID key field, changed "FieldToSum" to the field name.
I'm not sure how to use this on the subform or if it will take the ItemAmt and keep subtracting the PaidAmt.
Sorry, I don't understand how to use this.
Would you please explain...

NLR

 

[burrina]

In the forms record source, hopefully a query, adjust add this.

CurBal: CCur(Nz([ItemAmt],0)-Nz([PaidAmt]))

Then on the subform:

SomeField =RunSum([Form],"PrimaryKey",[PrimaryKey],"CurBal")

Of course PrimaryKey changed to YOUR Primary Key.

HTH

 

[NLR]

Hi,
The forms record source is a table.
The changes I made to your code above is as follows; did I miss anything?
Option Compare Database
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 = frm_Reclamation Info.RecordsetClone
' Find the current record.
Select Case rs.Fields(RecAcctID).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, _
DB_SINGLE, DB_DOUBLE, DB_BYTE
rs.FindFirst "[" & RecAcctID & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
rs.FindFirst "[" & RecAcctID & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
rs.FindFirst "[" & RecAcctID & "] = '" & 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(RecPendAmt)
' Move to the previous record.
rs.MovePrevious
Loop
Bye_RunSum:
RunSum = Result
Exit Function
Err_RunSum:
Resume Bye_RunSum
End Function

The second quote should be placed in the subform's record source or a new field's record source. I don't understand how this will work?
Thanks,
NLR

 

[burrina]

You DON'T change anything in the module code! ? !
Maybe upload a demo of your db.

 

[NLR]

Ok, I've attached a copy.

 

[burrina]

I will bow out as it has already been downloaded and I do not compete for posts!
Hopefully they will offer you a resolution.

Good Luck With Your Project!

[UNSUBSCRIBED]

 

[NLR]

Hello,
I believe you are seeing my view of the download. I wanted to make sure it was there.
Sorry, for the confusion.
Your help is still appreciated!
NLR