Checking Total

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a continuous form with a control called TotalFTE that sums the FTE
field from the records that are displayed in the form. This all seems to be
working properly.

My problem is that I want to check when the value of TotalFTE is 0 and
display a warning to the user. I have a statement like the following:

If TotalFTE.Value = 0 Then
[display message]
End If

I have tried putting this statement in the Change event of the FTE field,
the TotalFTE, even the form itself. In every case, the value of TotalFTE is
always zero and the message displays, even though I can see the amount on the
screen being correct.

Can someone give me an idea of where I am going wrong?

Thanks.
 
Martin said:
I have a continuous form with a control called TotalFTE that sums the FTE
field from the records that are displayed in the form. This all seems to be
working properly.

My problem is that I want to check when the value of TotalFTE is 0 and
display a warning to the user. I have a statement like the following:

If TotalFTE.Value = 0 Then
[display message]
End If

I have tried putting this statement in the Change event of the FTE field,
the TotalFTE, even the form itself. In every case, the value of TotalFTE is
always zero and the message displays, even though I can see the amount on the
screen being correct.
Click to expand...


You just can't do that. Access has several processing
threads going at different priorities so your code is
running before the control calculations are completed.
Screen repainting is a third thread with an even lower
priority.

The way to work with these optimizations is to either do all
the calculations in the same environment. In VBA you could
use DSum to calculate the total and then check it in the
same procedure.

If you don't like the overhead of using DSum or it's where
condions are too complex, then a different way of dealing
with the situation is to so it all in text box calculations.
Add another text box with a expression like:
=IIf(TotalFTE.Value = 0, "display message", "")
maybe even setting the message text box's Fore or Back Color
properties.

OTOH, if you don't like having a separate text box and can
live without the message, my preferred approach, is to use
Conditional Formatting on the total text box so that a zero
value is displayed with a red back color.
 
I guess I need to rethink this idea. It seems to be more complicated than I
thought.

Thanks for the help.

Marshall Barton said:
Martin said:
I have a continuous form with a control called TotalFTE that sums the FTE
field from the records that are displayed in the form. This all seems to be
working properly.

My problem is that I want to check when the value of TotalFTE is 0 and
display a warning to the user. I have a statement like the following:

If TotalFTE.Value = 0 Then
[display message]
End If

I have tried putting this statement in the Change event of the FTE field,
the TotalFTE, even the form itself. In every case, the value of TotalFTE is
always zero and the message displays, even though I can see the amount on the
screen being correct.
Click to expand...


You just can't do that. Access has several processing
threads going at different priorities so your code is
running before the control calculations are completed.
Screen repainting is a third thread with an even lower
priority.

The way to work with these optimizations is to either do all
the calculations in the same environment. In VBA you could
use DSum to calculate the total and then check it in the
same procedure.

If you don't like the overhead of using DSum or it's where
condions are too complex, then a different way of dealing
with the situation is to so it all in text box calculations.
Add another text box with a expression like:
=IIf(TotalFTE.Value = 0, "display message", "")
maybe even setting the message text box's Fore or Back Color
properties.

OTOH, if you don't like having a separate text box and can
live without the message, my preferred approach, is to use
Conditional Formatting on the total text box so that a zero
value is displayed with a red back color.
Click to expand...
 
Hi martin,

Have you tried using a custom format for the "TotalFTE" control?

In design view, open properties for the "TotalFTE" control. On the FORMAT
tab, set tien Format property to:

#,##0.00;(#,##0.00)[Red];"**Total is Zero**"[Red];"**NULL**"[Red]


This will display a positive number in black (two decimals), a negitive
number (two decimals) in parenthesis in red, a zero as "**Total is Zero**" in
red and a NULL value as "**NULL**" in red.


HTH
 
Back
Top