bobh wrote:
Hi All,
In Access97 I have a form setup as a continuous form several of the
bound controls have calculations that are done in the 'after update'
event via VBA code and all works fine.
My problem/question is if a user needs to go back to a previous line to
change a value who do I get the preceding lines to re-calculate. Since
a continuous form is more like a single record its confusing me.
example: on the continuous form I'm entering line/rows(whichever you
call them) and on line/row 10 and I notice that back on line/row 4 I
entered a value wrong. So, I click in the control and correct the value
and when I press 'enter' to kick off the 'after update' event code,
only line/row 4 adjusts. Lines/rows 5 thru 10 do nothing. Is it
possible to get them to update/adjust as well.
thanks
bobh.
You might want to set a flag if a recalc needs to be performed. If the
recalc flag is true, then perform the recacl.
In the BeforeUpdate event you might want to check if the record is new.
If so, the flag is false. IOW, check if the record is NOT new. You
can use OldValue to determine if the values have changed. OldValue is
only good for bound (to the table) controls.
If Not Me.NewRecord Then
If Me.Field1 <> Me.Field1.OldValue or _
Me.Field2 <> Me.Field2.OldValue then
blnRecalc = True
Endif
Now create a sub or function to recalc. It's possible that your form is
sorted or filtered so if you do a recalc, you could first set the order
to the way you expect it and remove any filters. Then set a recordset
to the form's recordset, go to the record in the recordset you are on in
the form and loop to the end, recalcing. Once done, restore the filter
and sort.