Instant Totals on Continuous Forms

[Ira]

I have a continuous form with each record containing 3 fields:
ORDERED, RECVD and a CHECKBOX named CLOSED. In the footer of the form
I have 2 fields that calculate totals for ORDERED AND RECVD using
SUM(). I am trying to get those totals to update instantly without
have to jump off the current record or REQUERY the whole form. When I
click the checkbox, it fills the RECVD field with the same number that
was in the ORDERED field. I want to "close" the order and lock the
form when the total ORDERED equals the total RECVD. But when I am on
on the last record of the continous form I have to move off the record
to get the totals to update. Is there a way to get the totals to
update while I am still on that current record (the focus is still on
the checkbox that I just checked)?

Thanks

 

[Stuart McCall]

I have a continuous form with each record containing 3 fields:
ORDERED, RECVD and a CHECKBOX named CLOSED. In the footer of the form
I have 2 fields that calculate totals for ORDERED AND RECVD using
SUM(). I am trying to get those totals to update instantly without
have to jump off the current record or REQUERY the whole form. When I
click the checkbox, it fills the RECVD field with the same number that
was in the ORDERED field. I want to "close" the order and lock the
form when the total ORDERED equals the total RECVD. But when I am on
on the last record of the continous form I have to move off the record
to get the totals to update. Is there a way to get the totals to
update while I am still on that current record (the focus is still on
the checkbox that I just checked)?

Thanks

Try either:

Me.Dirty = False '(save the record)

or:

Me.Recalc '(update all calculated controls)

in (say) the checkbox's AfterUpdate event.

 

[Marshall Barton]

I have a continuous form with each record containing 3 fields:
ORDERED, RECVD and a CHECKBOX named CLOSED. In the footer of the form
I have 2 fields that calculate totals for ORDERED AND RECVD using
SUM(). I am trying to get those totals to update instantly without
have to jump off the current record or REQUERY the whole form. When I
click the checkbox, it fills the RECVD field with the same number that
was in the ORDERED field. I want to "close" the order and lock the
form when the total ORDERED equals the total RECVD. But when I am on
on the last record of the continous form I have to move off the record
to get the totals to update. Is there a way to get the totals to
update while I am still on that current record (the focus is still on
the checkbox that I just checked)?

Sum only adds up the saved records. It can not guess that a
changed value will not be changed before it it saved. You
can force a record to be saved by using this line of VBA
code:
Me.Dirty = False

You did not say how you are using the Sum text boxes so a
word of caution may be in order. You can not use VBA code
to check the value of a calculated control. VBA code runs
asynchronously at a higher priority than the evaluation of
control expressions so the code will nearly aways run before
the calculations are done. And, No, there is no way to tell
when the calculations are finished. To keep code and
expression calculations synchronized, you need to either use
another control expression to check the result of the
calculation Or use VBA code to do the calculation and check
the result.

 

[Ira]

Sum only adds up the saved records.  It can not guess that a
changed value will not be changed before it it saved.  You
can force a record to be saved by using this line of VBA
code:
   Me.Dirty = False

You did not say how you are using the Sum text boxes so a
word of caution may be in order.  You can not use VBA code
to check the value of a calculated control.  VBA code runs
asynchronously at a higher priority than the evaluation of
control expressions so the code will nearly aways run before
the calculations are done.  And, No, there is no way to tell
when the calculations are finished.  To keep code and
expression calculations synchronized, you need to either use
another control expression to check the result of the
calculation Or use VBA code to do the calculation and check
the result.

You're right, it's still not working. Can't get my SUM() in a text box
to update first so that I can see if the total match and then close
the order. Any suggestions?

 

[Marshall Barton]

You're right, it's still not working. Can't get my SUM() in a text box
to update first so that I can see if the total match and then close
the order. Any suggestions?

As I said, you have two options. One for the check box to
use an expression like:
=Sum(ORDERED) = Sum(RECVD)
but that won't tell you what you want until the record is
saved and the check box's value will not be saved.

The other option is to not use control expressions to
calculate the sums. You can use code in the ORDERED and
RECVD text box's AfterUpdate event to calculate the sums:

Dim lngTotOrdered As Long, lngTotReceived As Long
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
lngTotOrdered = lngTotOrdered + Nz(!ORDERED, 0)
lngTotReceived = lngTotReceived + Nz(!RECVD, 0)
Loop
End With

Me.txtTotalORDERED = lngTotOrdered
Me.txtTotalRECVD = lngTotReceived
Me.chkClosed = (lngTotOrdered As Long = lngTotReceived)

Or maybe you want some variation of that in the check box's
AfterUpdate event?