Hi,

I have a continuous form showing 4 different text boxes. Three text boxes are bound to a table. 4th text box is unbound and has datasource as = [txtBox2] - [txtBox3]. I have code Me.Recalc in OnLoad event of the form.

This form has been placed as a subform in main form and height for this subform control is such that it displays 8 records (if there are more records then it provides vertical scroll bar in the subform).

What I have observed is that txtBox4 is calculated only for those records which are currently visible on the subform. For all the other records in subform which needs to be viewed using verticla scroll-bar, this control is not refreshed / calculated and when I click on this contol in Form View, it shows the calculated value

I even tried by using oncode to recalculate the fields by using code Me.Recalc on my main form OnLoad event. But still can not make it work

Do you have any suggestions?

Hi,

It is odd that the calculated control does not update automatically. I think placing Me.Recalc in the Load event of a form is unnecessary. Try taking it out to see what I mean.

Can you post a small copy of your db with test data?

Thanks theDBguy....even I was curious as to why is it happening...

After further debugging I noticed that in the control source of the calculated text control had the following formula:

= [txtBox2] - Nz([txtBox3])

If I remove Nz function from this formula and change to just = = [txtBox2] - [txtBox3] then the controls are getting updated properly with Me.Recalc

Not sure why Nz should cause this issue.

Rather than referring to the textboxes in the control source, why not directly referenece the fields that are bound to the respective boxes? Example: = [Field1] - [Field2]

Another option is to use a query as the recordsource for the subform, rather than a table. It would be something like this:

SELECT Field1, Field2, Field3, Field2 - Field3 AS Difference FROM myTable

The controlsource for your txtBox4 would be "Difference".

Hi psatkar,

Glad to hear you got it fixed. You might also try what Mark suggested.

Good luck with your project.

Thanks Mark,

Infact just to explain the logic which I have adopted, I gave txtBox1 and txtBox2 as examples....In reality I am using the underlying bound fields only for this purpose.

But yes, your suggestion of writing query is more practical. Let me adopt that.

Thanks....

psatkar