Problem with an Auto calculated field

[Steve67]

Wonder if I can get someone’s help with a problem I am having on where to place an IF statement in VBE. I have created a form in Access 2003 that has an automatic calculated field. Basically you enter information into other fields and the Running Total field automatic recalculates itself whenever the other data fields are updated. The problem I am having is that I need a message box to come up as soon as the Running Total reaches a certain point. Writing the IF statement I don’t think is the problem, but not sure what event to put it under. I have tried the AfterUpdate field under the Running Total properties, but have read that because the Running Total field is an automatic calculated field, that you can’t put IF statements there. It didn’t work when I did anyways. Below is kind of a sample of what I am trying to do:

Data Field 1 = 3
Data Field 2 = 8
Date Field 3 = 5
Date Field 4 = 2

Running Total = 18 PM Cycle =16

Running Total automatically adds up the Data Fields as soon as the operator enters in the numbers. I need to put an IF statement somewhere that will give me a message box to let the operator know as soon as the Running Total is >= to the PM Cycle. But like I have said before, I have tried to put an IF statement in various place, but it will not come up when the Running Total reaches the > = PM Cycle

Thanks
Steve

[MindBender77]

Are your DataFields and Running Total fields displayed as unbound textboxes on your form? Also, how are you trigging the auto calculate to run?

Let us know which type of controls you are using so we can better assist you.

Bender

[Steve67]

The data fields are bound to a table and the auto calculate feature is done through the query and the form is made from that query.

Let me know if there is anymore information you would need

Thanks
Steve

[MindBender77]

The data fields are bound to a table and the auto calculate feature is done through the query and the form is made from that query.

Let me know if there is anymore information you would need

Thanks
Steve

One possible solution would be to create a custom subroutine that contains your "if" statement. In the OnExit event of each data field, call this subroutine. This way, if at any point during the data entry,a message box will appear if the criteria you specified is met.

Hope the Helps,
Bender

[Steve67]

I was thinking about something a long those lines, but was trying to see if there was a short way of doing it without having to write extra code.

Thanks, I will give that a try

[MindBender77]

I was thinking about something a long those lines, but was trying to see if there was a short way of doing it without having to write extra code.

Thanks, I will give that a try

To shorten the code you could just call the subroutine from the OnExit event of the last data field. The problem with this is you would have to make sure that the focus at some point in the data entry would go through the last data field.
Bender

[Steve67]

Thanks for your help. I tried the OnExit event and so far it is working

Thanks
Steve