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

Validating a Form's data via calculated field

P: n/a
Hi all,

I have a form people use to enter checking data. One of the fields is
calculated based on finding the difference of two input fields on the form.
Here are the fields:

CheckAmount
AmountApplied
Funds Remaining

FundsRemaining = CheckAmount - AmountApplied

Can I prevent the form from allowing the user to commit the current record,
move on and create a new record until FundsRemaining = 0. If the user
clicks the New Record button, I'd like the form to check the FundsRemaining
field and pop a msgbox up if FundsRemaining <> 0. Any help would be greatly
appreciated!! :-)

Thank you.
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Certainly, in the BeforeUpdate event, do the calculation and if the result
isn't zero, set Cancel = True before exiting the event procedure. If you
have FundsRemaining as a calculated Control on the Form, of course, you can
just test it. Be sure you requery the calculated Control in the AfterUpdate
event of the CheckAmount or AmountApplied controls.

Larry Linson
Microsoft Access MVP

"Anonymous" <an*******@somewhere.far> wrote in message
news:daa7b.399454$uu5.73774@sccrnsc04...
Hi all,

I have a form people use to enter checking data. One of the fields is
calculated based on finding the difference of two input fields on the form. Here are the fields:

CheckAmount
AmountApplied
Funds Remaining

FundsRemaining = CheckAmount - AmountApplied

Can I prevent the form from allowing the user to commit the current record, move on and create a new record until FundsRemaining = 0. If the user
clicks the New Record button, I'd like the form to check the FundsRemaining field and pop a msgbox up if FundsRemaining <> 0. Any help would be greatly appreciated!! :-)

Thank you.

Nov 12 '05 #2

P: n/a
This works great! However, I have a me.recalc event that takes place when
other fields on the form are filled in (running realtime calculations as
funds are applied).

As a result, I get the msgbox "All Funds have not been applied" each time I
enter info on the form. I only need this message to pop up when the user
tries to move on and add a new record when there are still funds left to
apply.

More detail:

Check Amount Entered $500
Category1 Entered $100
Category2 Entered $200
Category3
FundsApplied Calc'd $300
FundsRemaining Calc'd $200

User should not be able to click "NewRecord" because FundsRemaining <>0.
Problem is I get the msgbox error message I programmed each time I enter
info in one of the categories and tab to the next field.

This is what I programmed:

If FundsRemaining <> 0 Then
Cancel = True
MsgBox "You have not applied all funds yet"
End If
I wonder how I can get around getting the message on each data input?

"Larry Linson" <la**********@ntpcug.org> wrote in message
news:1T*******************@nwrddc02.gnilink.net...
Certainly, in the BeforeUpdate event, do the calculation and if the result
isn't zero, set Cancel = True before exiting the event procedure. If you
have FundsRemaining as a calculated Control on the Form, of course, you can just test it. Be sure you requery the calculated Control in the AfterUpdate event of the CheckAmount or AmountApplied controls.

Larry Linson
Microsoft Access MVP

"Anonymous" <an*******@somewhere.far> wrote in message
news:daa7b.399454$uu5.73774@sccrnsc04...
Hi all,

I have a form people use to enter checking data. One of the fields is
calculated based on finding the difference of two input fields on the

form.
Here are the fields:

CheckAmount
AmountApplied
Funds Remaining

FundsRemaining = CheckAmount - AmountApplied

Can I prevent the form from allowing the user to commit the current

record,
move on and create a new record until FundsRemaining = 0. If the user
clicks the New Record button, I'd like the form to check the

FundsRemaining
field and pop a msgbox up if FundsRemaining <> 0. Any help would be

greatly
appreciated!! :-)

Thank you.


Nov 12 '05 #3

P: n/a

"Anonymous" <an*******@somewhere.far> wrote in message
news:daa7b.399454$uu5.73774@sccrnsc04...
Hi all,

I have a form people use to enter checking data. One of the fields is
calculated based on finding the difference of two input fields on the form. Here are the fields:

CheckAmount
AmountApplied
Funds Remaining

FundsRemaining = CheckAmount - AmountApplied

Can I prevent the form from allowing the user to commit the current record, move on and create a new record until FundsRemaining = 0. If the user
clicks the New Record button, I'd like the form to check the FundsRemaining field and pop a msgbox up if FundsRemaining <> 0. Any help would be greatly appreciated!! :-)

Thank you.


Go to: http://www.mvps.org/access/
Best Regards
Cody Martin, MVP
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.