Textbox behaves differently for similar Queries

[Bill Bisco]

I have 2 databases that have very similar queries yet 1 Sums up TextBox Values fine and the other Fails utterly.

It would take me a lot longer to explain rather than me just showing you. To get to where I'm talking about: Go to Welcome Form, and under the Button which says "View LS and RS Stations Together" click the Combo Box and Choose Station 1, 2, or 3.

In the OptionMix Database, the Blended Values display fine whereas in the ProposedMix Database, the Blended Values generate errors.

If someone could take a look at these and tell me what's wrong, I'd appreciate it.

Sincerely,
Bill

 

[RuralGuy]

Both mdb's have no "frmProcessSelectTogetherLS&RS" form and return Error #2102 when you press the button.

 

[wiklendt]

why don't you just copy working one (query/control?) from one database to the other? i often recycle code/queries this way.

 

[Bill Bisco]

Both mdb's have no "frmProcessSelectTogetherLS&RS" form and return Error #2102 when you press the button.

You're not supposed to press the button; you're supposed to click the combo box below that button and choose Station 1, 2, or 3. If you still have the energy to help me, and try looking again, I'd appreciate it.

@wiklendt

I agree with you and that is what I would like to do. In my opinion I have actually improved my code and method somewhat and am utterly confused as to why my change doesn't work as my 2 queries and calculations are so similar.

When I looked at it again, I did notice something interesting.

OptionMix in the first Database is calculated from 2 Long Integer Fields. However, the Final OptionMix presented is obviously not Long Integer because the calculated field has decimals.
ProposedMix in the second Database is Single

So this may have something to do with the problem, but I don't understand why that should matter

As always, any help is appreciated.

Sincerely,
Bill

 

[Bill Bisco]

I have found Allen Browne's article about misinterpreted calculated fields at http://allenbrowne.com/ser-45.html

However, even when going into SQL and wrapping the calculation in CSng(), I'm still getting errors.

Is there something else that I'm doing wrong?

Sincerely,
Bill

 

[Bill Bisco]

I have just discovered that apparently for Access2003, that if 1 Textbox tries to refer to another Textbox rather than the Control Source, then suddenly all my other textboxes get #Error Messages even if those Textboxes correctly refer to Control Sources as =Sum([ControlSource]).

Perhaps this is a key to my problem that was not mentioned anywhere else on the web that I found. Now I have to wonder; Why in the world would Access be programmed this way?

 

[RuralGuy]

This is simply a timing issue and the solution is for you to control the timing in code rather than letting Access try and figure out which control to complete first.

 

[Bill Bisco]

Thanks for the reply,

Alright, now to solve the timing issue though I must understand why it worked in one case and why it didn't work in the other. That's one thing I don't get. OptionMix and ProposedMix were both evaluated in subqueries before being used in the main query. And for some reason ProposedMix usage gets errors while OptionMix does not.

 

[Bill Bisco]

After Doing some work for another area of my database and running into other problems I suspect that the issue is one formula which has bad referencing. In my tests if one textbox had a bad reference then all of my textboxes suddenly got #Errors (even if they worked before).

That is an absolutely insane occurrence. I'll consider this issue solved for the time being. If I run into further problems, I may post again.

Sincerely,
Bill