Help With RunSum Function

[Junior]

I have a subform, in which I want to calculate a running total in the
details section (similar to the way you can do so in a report).

I have copied and pasted the RunSum function into my database, but I am
still having difficulty getting it to run - probably due to syntax.
Here are the parametres from the function:

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")

My subform is called [Frm_FG_Subform].
I'm assuming that the form's unique key field is a record number, or
autonumber of some sort assiciated with each record - my field is
called [Num]. The key value is the same as key name? The field I wish
to sum is [OTY]. So,

RunSum([Frm_FG_Subform],"Num",[Num],"QTY") - is this correct, becasue
when I run it, I get ?NAME.

 

[Guest]

Hi Junior,

You wanted to have a running sum for a field say "Qty" in your subform.

Then do the following.

Your subform name:[Frm_FG_Subform]

You have to create a new field in the record source table of your subform.

Say the new field name is "SumQty" ; data type - Number ; Field size - Long
Integer
remove 0 in the default value.

In subform design view, insert this new field (SumQty) next to "Qty"

Also create a new button in your Main form. say the button is named
"cmdRunSum".

In the on click event of this button enter the following code:

Private Sub cmdRunSum_Click()
Dim rst As DAO.Recordset
'assuming your subform control name is "Frm_FG_Subform" otherwise change it.
Set rst = Me.Frm_FG_Subform.Form.RecordsetClone
Dim curSum As Long

rst.MoveFirst
curSum = rst!Qty
rst.Edit
rst!SumQty = curSum
rst.Update

rst.MoveNext
Do Until rst.EOF
curSum = rst!Qty + curSum
rst.Edit
rst!SumQty = curSum
rst.Update
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
End Sub

I presume your subform is in datasheet view.

Open your Main form in normal view.

click the button "RunSum"(cmdRunSum)

You should see the running sum in "SumQty" column. The code given above is
self explanatory.

Like in reports, running sum for unbound controls will not work in forms.

While closing the form, you have to reset the values in "SumQty" field as
the data is permanent.

In the close event of the subform enter the following code.

Private Sub Form_Close()
Dim strSQL As String
'substitute your table name instead of "tblQty".
strSQL = "UPDATE tblQty SET tblQty.SumQty = Null "
strSQL = strSQL & "WHERE (((tblQty.SumQty) Is Not Null));"
DoCmd.RunSQL strSQL
'if you have created any update query instead of sql you can run the
query as under.
'DoCmd.OpenQuery "queryname"

End Sub

Goodluck,
Surendran

I have a subform, in which I want to calculate a running total in the
details section (similar to the way you can do so in a report).

I have copied and pasted the RunSum function into my database, but I am
still having difficulty getting it to run - probably due to syntax.
Here are the parametres from the function:

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")

My subform is called [Frm_FG_Subform].
I'm assuming that the form's unique key field is a record number, or
autonumber of some sort assiciated with each record - my field is
called [Num]. The key value is the same as key name? The field I wish
to sum is [OTY]. So,

RunSum([Frm_FG_Subform],"Num",[Num],"QTY") - is this correct, becasue
when I run it, I get ?NAME.

 

[SusanV]

Totals should never be inserted in tables! You can simply create an unbound
textbox in the subform's footer, and set the source to =Sum([ID])
--
hth,
SusanV

Hi Junior,

You wanted to have a running sum for a field say "Qty" in your subform.

Then do the following.

Your subform name:[Frm_FG_Subform]

You have to create a new field in the record source table of your subform.

Say the new field name is "SumQty" ; data type - Number ; Field size -
Long
Integer
remove 0 in the default value.

In subform design view, insert this new field (SumQty) next to "Qty"

Also create a new button in your Main form. say the button is named
"cmdRunSum".

In the on click event of this button enter the following code:

Private Sub cmdRunSum_Click()
Dim rst As DAO.Recordset
'assuming your subform control name is "Frm_FG_Subform" otherwise change
it.
Set rst = Me.Frm_FG_Subform.Form.RecordsetClone
Dim curSum As Long

rst.MoveFirst
curSum = rst!Qty
rst.Edit
rst!SumQty = curSum
rst.Update

rst.MoveNext
Do Until rst.EOF
curSum = rst!Qty + curSum
rst.Edit
rst!SumQty = curSum
rst.Update
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
End Sub

I presume your subform is in datasheet view.

Open your Main form in normal view.

click the button "RunSum"(cmdRunSum)

You should see the running sum in "SumQty" column. The code given above is
self explanatory.

Like in reports, running sum for unbound controls will not work in forms.

While closing the form, you have to reset the values in "SumQty" field as
the data is permanent.

In the close event of the subform enter the following code.

Private Sub Form_Close()
Dim strSQL As String
'substitute your table name instead of "tblQty".
strSQL = "UPDATE tblQty SET tblQty.SumQty = Null "
strSQL = strSQL & "WHERE (((tblQty.SumQty) Is Not Null));"
DoCmd.RunSQL strSQL
'if you have created any update query instead of sql you can run the
query as under.
'DoCmd.OpenQuery "queryname"

End Sub

Goodluck,
Surendran

I have a subform, in which I want to calculate a running total in the
details section (similar to the way you can do so in a report).

I have copied and pasted the RunSum function into my database, but I am
still having difficulty getting it to run - probably due to syntax.
Here are the parametres from the function:

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")

My subform is called [Frm_FG_Subform].
I'm assuming that the form's unique key field is a record number, or
autonumber of some sort assiciated with each record - my field is
called [Num]. The key value is the same as key name? The field I wish
to sum is [OTY]. So,

RunSum([Frm_FG_Subform],"Num",[Num],"QTY") - is this correct, becasue
when I run it, I get ?NAME.

 

[Guest]

Hi Susan,

I agree for grand totals and sub totals, we can use footer.

but the question asked is running total something like this.

qty sumqty
10 10
20 30
15 45

So I have simply given a workaround to get the running sum.

Surendran

Totals should never be inserted in tables! You can simply create an unbound
textbox in the subform's footer, and set the source to =Sum([ID])
--
hth,
SusanV

Hi Junior,

You wanted to have a running sum for a field say "Qty" in your subform.

Then do the following.

Your subform name:[Frm_FG_Subform]

You have to create a new field in the record source table of your subform.

Say the new field name is "SumQty" ; data type - Number ; Field size -
Long
Integer
remove 0 in the default value.

In subform design view, insert this new field (SumQty) next to "Qty"

Also create a new button in your Main form. say the button is named
"cmdRunSum".

In the on click event of this button enter the following code:

Private Sub cmdRunSum_Click()
Dim rst As DAO.Recordset
'assuming your subform control name is "Frm_FG_Subform" otherwise change
it.
Set rst = Me.Frm_FG_Subform.Form.RecordsetClone
Dim curSum As Long

rst.MoveFirst
curSum = rst!Qty
rst.Edit
rst!SumQty = curSum
rst.Update

rst.MoveNext
Do Until rst.EOF
curSum = rst!Qty + curSum
rst.Edit
rst!SumQty = curSum
rst.Update
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
End Sub

I presume your subform is in datasheet view.

Open your Main form in normal view.

click the button "RunSum"(cmdRunSum)

You should see the running sum in "SumQty" column. The code given above is
self explanatory.

Like in reports, running sum for unbound controls will not work in forms.

While closing the form, you have to reset the values in "SumQty" field as
the data is permanent.

In the close event of the subform enter the following code.

Private Sub Form_Close()
Dim strSQL As String
'substitute your table name instead of "tblQty".
strSQL = "UPDATE tblQty SET tblQty.SumQty = Null "
strSQL = strSQL & "WHERE (((tblQty.SumQty) Is Not Null));"
DoCmd.RunSQL strSQL
'if you have created any update query instead of sql you can run the
query as under.
'DoCmd.OpenQuery "queryname"

End Sub

Goodluck,
Surendran

I have a subform, in which I want to calculate a running total in the
details section (similar to the way you can do so in a report).

I have copied and pasted the RunSum function into my database, but I am
still having difficulty getting it to run - probably due to syntax.
Here are the parametres from the function:

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")

My subform is called [Frm_FG_Subform].
I'm assuming that the form's unique key field is a record number, or
autonumber of some sort assiciated with each record - my field is
called [Num]. The key value is the same as key name? The field I wish
to sum is [OTY]. So,

RunSum([Frm_FG_Subform],"Num",[Num],"QTY") - is this correct, becasue
when I run it, I get ?NAME.