Excessive calculating
We have a form in Access 2000 that has a number of mathematical functions on it. It was working fine, but we recently added some new items to the form, and now whenever we open the form it displays #Error? in all the controls with maths functionality. I've checked all the code and can see nothing wrong. I enclose a printout of the mathematical functions, as there are only five on the form, and wonder if you can see what the problem is.
I'm glad you didn't have more functions in the code, but it wouldn't matter because I already had a good idea about what the problem is, having seen it before. Your problem is you have one control that gets its result from an expression calculated within another control, and you can't do that for certain functions. Your situation is quite complex because of the way you've structured the data, so I'll use a simple example to explain what's wrong. Let's say you had a control on a form with a ControlSource and ControlName of 'Boxes'. Now lets say you place a text control on your form, with a name of 'Text1 ' and a ControlSource of ' = Sum([Boxes])'. So far all is well but, were you to add a second textbox to the form and set its ControlSource to '= Sum([Text1])', you'd see that your error instantly appears, and that's because you can't use a calculated function (in this case 'SumO') on a calculated expression.