code only works in debug mode

[Guest]

This one baffles me.
The two text boxes get their values from me setting the Control Source
property for each to '=Sum(Collection)'. Collection is a field in the query
this is based on.
When i run the code I get an Overflow error. Err#6.
When I set a breakpoint on the first line of code then step through it, it
works just fine. The variables get set to the proper values, the math works
and the txtPerHour box displays the result. In run mode sngTotalHours is
Empty, curTotalCollection = 0 so then I get the overflow error trying to
divide by zero!
Anyone know what is going on here?

Private Sub Form_Current()

Dim sngTotalHours, sngAverage As Single
Dim curTotalCollection As Currency

lblProp.Caption = strNB
sngTotalHours = txtTotalHours.Value
curTotalCollection = txtTotalCollection.Value
sngAverage = curTotalCollection / sngTotalHours
txtPerHour.Value = Format(sngAverage, "$##.00")

End Sub

Thanks, Bart

 

[Wayne Morgan]

The code runs before the form is done calculating the textboxes. There are a
few ways to work around this.

1) Set up a loop that includes a DoEvents statement in the code. The
DoEvents will allow Access to continue calculating the textboxes. This loop
should check for a value in the textboxes. When a value exists, exit the
loop.

2) Redo the calculation in the code instead of getting the value from the
textboxes.

3) Fill the textboxes from the code instead of their Control Source.

4) Do the calculation in the query feeding the form and bind the textboxes
to the calculated fields in the query.

Which option you choose depends on what else you are doing. #1 is the most
problematic and I don't recommend it.

 

[Guest]

Thanks for the reply, this forum has been more helpful to me than all my
references combined!

I Agree, #1 should only be used as a last resort. Way too cumbersome.

In the meanwhile I came up with a simple solution that took me awhile to
find only because of it's simplicity...

The Control Source property for txtPerHour. I set it as
'=Sum([Collection])/Sum([Hours])!!

Bart