Very slow textbox expression

[Leslie Isaacs]

Hello All

I have a form that opens in continuous form view, where I need a textbox
[balance] to display the running sum of [amount credit]-[amount]. As there
is no 'running sum' property for a form's textbox (as there is on a report's
texbox), I have had to create an expression for the control source of
[balance] to do the job:

=DSum("[payments]![amount
credit]-[amount]","[payments]","([payments]![stmt_date]<[stmt_datee] or
([payments]![stmt_date]=[stmt_datee] and [payments]![id1]<=[id11])) and
[payment date]>#31/3/08#")

The criteria for this DSum expression, combined with the specific ordering
of the records on the form, ensures that the displayed balance corresponds
correctly to each record - and it works perefctly - except that it can take
over 30 seconds for [balance] to be displayed for every record. When the
form opens, the [balance] field on every record is initially blank. After a
few seconds they start to fill in, but even with just ~100 records it can
take over 30 seconds for them all to be displayed.

Is there a better way of doing this?
What a pity there isn't a 'running sum' property for a form's textbox as
there is on a report's texbox!! (or is there??)

Hope someone can help.
Many thanks
Les

 

[Mark A. Sam]

Hello Leslie,

There is a better way, but I can't find an example right now. I don't
remember where I did it, but I added a field to the underlying table to hold
the value, then I intereated through the recordsetclone of the form, adding
up the running sum at each iteration, and assigning it to the new field. If
I think of where the code is, I'll post it.

God Bless,

Mark A.Sam

 

[Mark A. Sam]

Here is an example of re-numbering a field, [NumericOrder], in a form

Instead of i as an Integer, Dim it as the number type you are using, like
Currency.


Public Sub Reorder()
On Error Resume Next

Dim i As Integer
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
i = i + 1
.Edit
![NumericOrder] = i
.Update
.MoveNext
Loop
End With

 

[Mark A. Sam]

Here is a rewrite for your situation that should work for you. It isn't
tested, but looks right. You need to as a field called Balance to your table
and set it as the ControlSource of your [Balance] textbox.

Here is an example of re-numbering a field, [NumericOrder], in a form

Instead of i as an Integer, Dim it as the number type you are using, like
Currency.

Public Sub RunningSum()
On Error Resume Next

Dim cBal As Currency
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
cBal = cBal + ([amount credit]-[amount])
![Balance] = cBal>
.Update
.MoveNext
Loop
End With

End Sub