By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,504 Members | 1,190 Online
Bytes IT Community
+ Ask a Question
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

✓ answered by jimatqsi

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

Share this Question
Share on Google+
6 Replies


Expert 100+
P: 1,240
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
Mar 27 '14 #2

P: 67
Hi Jim,

Thanks you so much for your code.
Mar 27 '14 #3

NeoPa
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

NeoPa
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

Post your reply

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