By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,852 Members | 955 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,852 IT Pros & Developers. It's quick & easy.

Problem with an Auto calculated field

P: 20
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
Apr 8 '08 #1
Share this Question
Share on Google+
6 Replies


MindBender77
100+
P: 234
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
Apr 8 '08 #2

P: 20
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
Apr 8 '08 #3

MindBender77
100+
P: 234
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
Apr 8 '08 #4

P: 20
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
Apr 8 '08 #5

MindBender77
100+
P: 234
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
Apr 8 '08 #6

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

Thanks
Steve
Apr 9 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.