Validating a Form's data via calculated field

[Anonymous]

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.

[Larry Linson]

Re: Validating a Form's data via calculated field

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" <anonymous@some where.far> wrote in message
news:daa7b.3994 54$uu5.73774@sc crnsc04...[color=blue]
> 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[/color]
form.[color=blue]
> Here are the fields:
>
> CheckAmount
> AmountApplied
> Funds Remaining
>
> FundsRemaining = CheckAmount - AmountApplied
>
> Can I prevent the form from allowing the user to commit the current[/color]
record,[color=blue]
> 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[/color]
FundsRemaining[color=blue]
> field and pop a msgbox up if FundsRemaining <> 0. Any help would be[/color]
greatly[color=blue]
> appreciated!! :-)
>
> Thank you.
>
>[/color]

[JBrow]

Re: Validating a Form's data via calculated field

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" <larry.linson@n tpcug.org> wrote in message
news:1Ta7b.3588 0$Dg7.14979@nwr ddc02.gnilink.n et...[color=blue]
> 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[/color]
can[color=blue]
> just test it. Be sure you requery the calculated Control in the[/color]
AfterUpdate[color=blue]
> event of the CheckAmount or AmountApplied controls.
>
> Larry Linson
> Microsoft Access MVP
>
> "Anonymous" <anonymous@some where.far> wrote in message
> news:daa7b.3994 54$uu5.73774@sc crnsc04...[color=green]
> > 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[/color]
> form.[color=green]
> > Here are the fields:
> >
> > CheckAmount
> > AmountApplied
> > Funds Remaining
> >
> > FundsRemaining = CheckAmount - AmountApplied
> >
> > Can I prevent the form from allowing the user to commit the current[/color]
> record,[color=green]
> > 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[/color]
> FundsRemaining[color=green]
> > field and pop a msgbox up if FundsRemaining <> 0. Any help would be[/color]
> greatly[color=green]
> > appreciated!! :-)
> >
> > Thank you.
> >
> >[/color]
>
>[/color]

[Cody Martin]

Re: Validating a Form's data via calculated field


"Anonymous" <anonymous@some where.far> wrote in message
news:daa7b.3994 54$uu5.73774@sc crnsc04...[color=blue]
> 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[/color]
form.[color=blue]
> Here are the fields:
>
> CheckAmount
> AmountApplied
> Funds Remaining
>
> FundsRemaining = CheckAmount - AmountApplied
>
> Can I prevent the form from allowing the user to commit the current[/color]
record,[color=blue]
> 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[/color]
FundsRemaining[color=blue]
> field and pop a msgbox up if FundsRemaining <> 0. Any help would be[/color]
greatly[color=blue]
> appreciated!! :-)
>
> Thank you.
>
>[/color]

Go to: http://www.mvps.org/access/
Best Regards
Cody Martin, MVP