Running sum on subform, dsum is the way?

[Egglar]

Hello.

Ive been trying to put a running sum on a subform, ive searched around quite a bit and found dsum is used quite frequently.

My subform is based on a query, names: sfrmTransaction, qryTransaction, frmTransaction. Fields: Amount, TransactionID.

Where is the dsum expression best to go? On the sub form its self, or on the query?

Also how do i make the actual running sum expression?

I dont like to be "spoon fed" code, but in this case im completly at a loss, i cant even visualise how i would get around making a running sum from the dsum property.

Thanks in advance, Elliot.

 

[RichD]

Elliot,

I used recordsetClone to update the total each time the price or quantity ordered changed anywhere in the subform. here is the code:

Private Sub Form_AfterUpdate()

Dim orderset As Recordset
Dim ordertotal As Variant
Set orderset = Me.RecordsetClone
orderset.MoveFirst
ordertotal = 0

Do Until orderset.EOF
ordertotal = (orderset![QTY_ORD] * orderset![COST_EACH] + ordertotal
orderset.MoveNext
Loop

'update total order value on main form
Forms![Porder entry]![Total Order Value] = ordertotal


End Sub


Let me knw if you have any problems.

Rich

Lead Developer

http://www.blsoftware.co.uk