Calculating a Running Sum???

[Donald King]

Report forms have a running sum property for calculating a running sum of a
total field. I took a stab at writting my own RunSum procedure for a form
but just shows the total all the way down the form. This form is connected
to a Query with a one to many relationship of Stock Items on the main form
and Transactions in the sub-form. In the subform's code I wrote the falling
procedure and added the RunSum in the AfterUpdate events of the
txtbxUnitsReceived and the txtbxUnitsXferred:

Private Sub RunSum()
Dim nSum As Integer
With Me.RecordsetClone
.MoveFirst
While Not .EoF
nSum = nSum + Me.UnitsReceived 'Items received to the equip. yard
nSum = nSum - Me.UnitsXferred 'Items transferred out of the yard
Me.txtbxUnitsOnHand = nSum
.MoveNext
Wend
End With
End Sub

Any suggestions would be greatly appreciated,
Don

 

[Allen Browne]

I assume that this is a continuous subform (or datasheet), and that
txtbxUnitsOnHand is unbound (since you are assigning nSum to it.) You
probably already found that the assigned value then shows on every row, i.e.
an unbound control cannot have different values on different rows of a
continuous form.

There is no simple and efficient way to create a running sum in a continuous
form, if the form can be filtered and sorted at will and the data needs to
be editable.

If you don't mind a slow form, you can use a DSum() expression in the
Control Source of your text box, e.g.:
=DSum("[UnitsReceived] - [UnitsXferred]", "Table1", "ID <= " &
Nz([ID],0))

If you don't mind a read-only result, you can use a subquery in the form's
source query.

If you only need to show the results for the current row, you could put a
text box into the Form Footer section of your continuous form, and either
use your code or DSum() which should be efficient enough for calculating a
single row.

 

[Donald King]

Allen,
I had tried the DSum function before I wrote the routine below and that
just puts the Grand Total in each txtbxRunSum. Here's what I did with the
DSum:

=DSum("nz([UnitsReceived])-nz([UnitsXferred])","dbo_InvTransactns","[StockID]="
& Forms![Items]![StockNo]). This not much different than what I do to
display the grand total on hand at the top of the form by just using the
=Sum(nz([UnitsReceived])-nz([UnitsXferred])). I think I'll give up on this
one or come back later. Thanks much.
Don
-------------

I assume that this is a continuous subform (or datasheet), and that
txtbxUnitsOnHand is unbound (since you are assigning nSum to it.) You
probably already found that the assigned value then shows on every row, i.e.
an unbound control cannot have different values on different rows of a
continuous form.

There is no simple and efficient way to create a running sum in a continuous
form, if the form can be filtered and sorted at will and the data needs to
be editable.

If you don't mind a slow form, you can use a DSum() expression in the
Control Source of your text box, e.g.:
=DSum("[UnitsReceived] - [UnitsXferred]", "Table1", "ID <= " &
Nz([ID],0))

If you don't mind a read-only result, you can use a subquery in the form's
source query.

If you only need to show the results for the current row, you could put a
text box into the Form Footer section of your continuous form, and either
use your code or DSum() which should be efficient enough for calculating a
single row.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Report forms have a running sum property for calculating a running sum of
a
total field. I took a stab at writting my own RunSum procedure for a form
but just shows the total all the way down the form. This form is
connected
to a Query with a one to many relationship of Stock Items on the main form
and Transactions in the sub-form. In the subform's code I wrote the
falling
procedure and added the RunSum in the AfterUpdate events of the
txtbxUnitsReceived and the txtbxUnitsXferred:

Private Sub RunSum()
Dim nSum As Integer
With Me.RecordsetClone
.MoveFirst
While Not .EoF
nSum = nSum + Me.UnitsReceived 'Items received to the equip. yard
nSum = nSum - Me.UnitsXferred 'Items transferred out of the
yard
Me.txtbxUnitsOnHand = nSum
.MoveNext
Wend
End With
End Sub

Any suggestions would be greatly appreciated,
Don