During input calculation to get sum value

[sophannaly]

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

[jimatqsi]

Sophannaly,
In the BeforeUpdate event of each amount text box put this:

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

[sophannaly]

Hi Jim,

Thanks you so much for your code.

[NeoPa]

Rather than using DoCmd.CancelEve nt, 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.

[sophannaly]

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

[NeoPa]

More than happy to help Sophanna :-)

[jimatqsi]

Thanks for the tip, NP!

Jim