Calculated Controls (1 Viewer)

[tmyers]

How "deep" can you go with calculated controls?

I have a sub-form with a calculated control, whose control source is a query which is also doing a calculation. I have another control which I was trying to do ANOTHER calculation based off the previous control, but it won't return the desired value. It only returns the value based off the "first" layer, the calculation being done within the query and not the calculation being done within the previous control.

Layer 1 is the base query
Layer 2 is the first control based off the query
Layer 3 is the second control based off the first control - this is where it seems to break.

Am I doing this wrong? Or is this not something I can do? This is a sub-form that is just displaying a breakdown of values along with totals, hence all the calculations being done.

Would this maybe require another query based off the first query?

 

[The_Doc_Man]

Look up the Me.ReCalc method as a way to force a re-computation when you have a sequence of computations. The first one should be invariant since it depends on a query. The second one, however, depends on the contents of another control. If the evaluation is in the "wrong order" then you get what you are describing. A Recalc would probably have no effect on the first control in the sequence but would alter the second control's contents.

Theoretically, though, you probably WOULD do better if you found a way to base the errant control on something outside of the form - like a field in a query. But as a "quick-and-dirty" way, try the .Recalc first. If it works, great! If not, it was at worst a one-line action, not that hard to implement. I'm guessing that you would put that .ReCalc near the end of a Form_Current routine if some the controls in question are bound.

 

[tmyers]

Look up the Me.ReCalc method as a way to force a re-computation when you have a sequence of computations. The first one should be invariant since it depends on a query. The second one, however, depends on the contents of another control. If the evaluation is in the "wrong order" then you get what you are describing. A Recalc would probably have no effect on the first control in the sequence but would alter the second control's contents.

Theoretically, though, you probably WOULD do better if you found a way to base the errant control on something outside of the form - like a field in a query. But as a "quick-and-dirty" way, try the .Recalc first. If it works, great! If not, it was at worst a one-line action, not that hard to implement. I'm guessing that you would put that .ReCalc near the end of a Form_Current routine if some the controls in question are bound.

All but 2 of 8 controls on this form are bound. 6 are bound to the base query which does several calculations (mostly summing and such, nothing complicated). The other 2 are based off the other controls. One does a simple sum of all the other controls and has not had any issues. The second one is the problem child. It is a simple percentage calculation (1-(txt1/txt2)), but txt1 (obviously these are just example names) is that first unbound control that is summing the other controls. I get the base value of txt1, but not the summed values. In other words, it seems the second control calculates its value before the first control does its calculation causing that desync.

I did try the me.recalc per your suggestion. I tried it in both On Current and On Load, but the result did not change.

 

[MajP]

Often in Access you cannot do a calculation on a calculated control. For example if
Control 1: = txtPrice * txtAmount
Control 2: = sum([control 1])
may fail
often have to do
control 2 = Sum(txtPrice * txtAmount)

Not sure if this is the case. The above depends on where the controls are located. Same section, different section, etc.
However this does not include calculations done in a query. So this might not be your issue.

See article

Jan's Access Forms & Reports: Totals of Calculated Values