#Error! is only displayed sometimes on calculated textbox

[qwertyjjj]

I have a form with loads of calculated textboxes (using expressions).
Some of these also rely on other textboxes that are being calculated when the form loads.
If I load it once, some of the textboxes display #Error! but most don't.
On closing it and reloading it a second time, the textboxes that displayed #Error! the first time now display a correct value and some other textboxes display #Error! instead.

Occasionally, every control works perfectly.

Is this something to do with when the calculation on each control is run and that if the dependent calculations don't finish quickly enough?

If so, is there a way round it?

 

[boblarson]

For one thing, are your textboxes named something other than the field name. If you have the control and the field name the exact same name, when you go to do calculations it will not like it as it can't figure out which you want to refer to.

Microsoft is nice (he says sarcastically) and when you use the form wizard, to create a form, it names the control the same as the field name. It's a good idea to change all of your bound controls to add a prefix to the name, like txt for text boxes, cbo for combo boxes, etc. But, it's even more important to do so when you are using those controls in calculations.

 

[qwertyjjj]

Yeah, they all have prefixes like cb_ or txt_ etc.

The funny thing is is that the textboxes that display #Error don't always display it when I reload the form or refresh them (manually via code).
I think it's because the hidden control boxes have not finished calculating by the time these control boxes need to use their values...but I can't see any way round this.

?

 

[boblarson]

I guess it could be a timing issue, though I'm not sure about that. I've always run across the issue of naming with those errors. Are you referring to the control or the field when doing the calculations?

 

[qwertyjjj]

Yeah, the controls (textboxes).
For example take the following controls:
A B C D

B, C, and D are hidden but A (visible) cannot be calculated correctly until B,C, and D have finshed retrieveing values from the DB, doing some addition, rounding, etc.

As it is a Detail section of a form, the A B C D rows are repeated about 200 times.

If I use a command button at the top of the form called cmd_Refresh, which I run after it has first finished calculating, and runs the following code:
txt_A.Requery
txt_B.Requery
etc.

then all the #Error boxes suddenly get recaluclated and have a value filled in correctly.

Strange ?!

So, my only work around at the moment that I can think of is to run this refresh automatically when the form has finished calculating for the first time. However, there is no way that I know of of finding out whether a form has finished its calculations/processing when it's loading ?!??!

 

[qwertyjjj]

ANyone know of a way to refresh the form to correct all these #Error boxes ?

 

[boblarson]

Any chance you could throw a stripped down version of your database up here so I can see if I can spot what's happening? I am more visual and sometimes I can spot the trouble better looking at it than trying to think of what is happening and all.

I just thought I'd ask as this one has me scratching my head, but I would like to help solve it.