459,504 Members | 1,190 Online
Need help? Post your question and get tips & solutions from a community of 459,504 IT Pros & Developers. It's quick & easy.

# During input calculation to get sum value

 P: 67 Hi, In my continuous form, I have few textbox fields like: Sum amount1 amount2 amount3 Sum is the value that user can't change. User need to input value for amount1, amount2, and amount3. But these three values summation can't exceed Sum value. Can anybody point me out the good way to go this? Best regards, Sophanna Mar 27 '14 #1

Sophannaly,
In the BeforeUpdate event of each amount text box put this:
Expand|Select|Wrap|Line Numbers
1. if nz(me.aount1,0)+nz(me.amount2,0)+nz(me.amount3,0)>me.Sum then docmd.cancelevent
That will prevent any entry that makes the total go over Sum. You might want to add a message box to tell what is happening.

Jim

6 Replies

 Expert 100+ P: 1,240 Sophannaly, In the BeforeUpdate event of each amount text box put this: Expand|Select|Wrap|Line Numbers if nz(me.aount1,0)+nz(me.amount2,0)+nz(me.amount3,0)>me.Sum then docmd.cancelevent That will prevent any entry that makes the total go over Sum. You might want to add a message box to tell what is happening. Jim Mar 27 '14 #2

 P: 67 Hi Jim, Thanks you so much for your code. Mar 27 '14 #3

 Expert Mod 15k+ P: 31,768 Rather than using DoCmd.CancelEvent, you can simply set the the defined variable [Cancel] to True. This is how BeforeUpdate events are designed to work. Both methods will work, of course, but using [Cancel] ensures all the code is explicitly working on the current object. Mar 27 '14 #4

 P: 67 Hi NeoPa, Thanks you so much and before I used to wonder what Cancel variable use for and now I know it from you. Sophanna Mar 28 '14 #5

 Expert Mod 15k+ P: 31,768 More than happy to help Sophanna :-) Mar 28 '14 #6

 Expert 100+ P: 1,240 Thanks for the tip, NP! Jim Mar 28 '14 #7