force recalculate on return to continuous form 1

[telephoto]

Using Access 2013 I have a continuous form with an SQL string recordsource showing a summary of investments.
The total value of a fund is displayed in a calculated textbox from two other fields, number of units x unit value.

To reset the current value I open a form for that record, amend the values in the table, record the changes in an historic table, then close and return to the continuous form. This performs a requery to refresh the data and resets to the chosen record.

The continuous form updates all fields except the calculated field.

Question, how can I force this to recalculate, or do I make another field in the table to store current total value?

Thanks in advance for your interest.

 

[tunsarod]

I'm guessing the continuous form remains open while you update the records otherwise if you had closed it and then re-opened it the calculated field would calculate the value. I suggest that in the continuous form you add an 'On Activate' procedure with a simple line that names the field with the .Requery method. e.g.

Private Sub Form_Activate()
myfield.Requery
End Sub

Of course this will only operate once when the form regains prominence on the screen. Alternatively put the bsame instruction in the OnCurrent event for the form. e.g.

Private Sub Form_Current()
myfield.Requery
End Sub

Personally I'm still working with earlier versions of Access so hopefully MS haven't done away with these methods in favor of some new fangled idea.

 

[MajP]

You should use the recalc method. See article for good discussion of recalc, repaint, refresh, and requery.

http://www.projectperfect.com.au/blog/microsoft-access-requery-recalc-refresh-and-repaint/

But I have to suspect there is more to it. If in fact the form requeries then it should recalc as well. Requery is the most powerful and the others are included actions when it requeries. My only guess is that you are in fact not requerying the continouse form.

 

[telephoto]

Thank you both for the replies.

MajP was on the ball when he said it wasn't being requeried, I forced a VBA break and stepped through the sequence.

Moved all the On_Current code to On_Activate and it worked without having to specify a recalc on the textbox.
until my next question....
Telephoto

 

[missinglinq]

Know this is a little old, but just to clarify, in case someone else in search of answers comes by!

ReCalc, as MajP suggested, is all that is really required, here, although Requery does recalculate the needed Fields, as well. You don't need 'specify a recalc on the textbox,' but simply

Me.ReCalc

which recalculates all Calculated Fields.

The difference in using ReCalc vs Requery, is that the former will leave Focus on the Current Record, while the latter will move Focus to the first Record in the Recordset, which may or may not be a problem.

The Missinglinq

Richmond, Virginia

The Devil's in the Details!