Validation Rule on a calculated field?

[wilpeter]

In an input form called "FrmReceipts" that feeds a table called "TblReceipts", one of the fields is called "OverUnder". This field has as its Control Source a formula: [RunningTtl] - [PaymentAmount]. There are several money fields in the form which must total the payment, so the "OverUnder" field is constantly showing the outstanding difference between the payment and the purchases entered. Upon completion of input, this "OverUnder" should be Zero.
Although I have the background colour changed (Conditional formatting) to show it is Out of Balance (not Zero), I can't stop a touch-typist (who doesn't look at the screen) from exiting to the next record while it is not in balance.
The Validation Rule doesn't seem to do anything when it is not a field that the user has to visit. Any suggestions? Thanks folks.

 

[John Big Booty]

You could check simply duplicate the calculation in your Data Entry field's Before Update event. It might look something like;

     If Me.YourField1 + Me.YourField2 <> Me.YourCheckField Then [COLOR="Green"]'Replace with your calculation[/COLOR]
        MsgBox "imbalance"
        DoCmd.CancelEvent
    End If

 

[wilpeter]

I wonder if there is some kind person, who understanding that answer, will interpret it for me. To which Data Entry Field's properties is he referring? As this is a calculated field, the keyer does not visit that field. The last field input is quite random, depending on the purchases and the formula that generates the running total is being updated with every sales field given a value. Thanks again.

 

[John Big Booty]

You talk about preventing a touch typist from tabbing to a new record. So the code could go in the last field your user visits on the form or better still in the Form's Before Update event.

Given that you are worried about an an unbound field being out of balance, you can simply test the same calculation that is populating your unbound text box in the event.

 

[wilpeter]

Thank you for your patience, but I haven't the skill apparently to make the situation clear. To recap: the first money field is the PaymentAmount--let's say $100 is keyed there. By tabbing two or three times one would reach the CourseFee field and enter say $50 - at which point the calculated field called RunningTotal displays $50. and the OverUnder field displays $50. By mouse or by tab, the next field visited might be the MemberFee field where $40 would be entered - and the RunningTotal field changes to $90. At this point, the calculated field called OverUnder is displaying (PaymentAmount-RunningTotal) $10 and is still 'out of balance'. The next field visited by tab or mouse might be MiscPurchase where $10 is entered - which changes the two calculated fields to $100 and $0 respectively, and puts the RECORD in balance. If the keyer now EXITS the record, all is well with the world.
If instead, an incorrect amount has been input or a different field is next visited and an amount entered, the RECORD will be out of balance. There are eight possible money fields which might be input with positive or negative amounts, so there is no known 'last field'.
Were I to put a formula in the Record's Properties, either before or after update, wouldn't the very first automatic calculation trigger an imbalance?
RunningTotal's Control Source is [CourseFee]+[MemberFee]+[MiscSale]+[etc]+[etc]... so there could be eight update events in that field and thereby the record. Can the record's "On Close" property work? Even though the form is not being Closed by X? I'm appreciating your time and learning as I go but I've only been using Access 2003 since, well, 2003. Thanks again.

 

[Bryan]

I'm thinking that you have to force the user to close the form using a command button by removing the default "x". The code in the command button should test to insure OverUnder = 0. If it does, save the record and close the form. If it doesn't, open an advisory msgbox followed by setting the focus to some control.

 

[John Big Booty]

If you use the Form's Before Update event as I believe I mentioned as a better alternative (along with the code mentioned in my first post) , this will prevent the user progressing to a new record whilst the current record is not in balance. Additionally it will not allow the user to close the form and save the record if the use the close (X) button, and if this is a concern you could take Bryan's recommendation and set the Close Button property to No.

If you had even had a cursory experiment with the code and advise you might have discovered these things for yourself :banghead:

 

[wilpeter]

I'm sorry if I'm frustrating you, but I did try to figure out the code. I tried
If Me.OverUnder <> 0 Then
MsgBox "Out of Balance"
DoCmd.CancelEvent
End If

This works to put up a message when out of balance, but needs something to proceed to next blank record (I'm in Add mode). I have managed for years with macros and never was obliged to use SQL or VB so this might sound a little immature a question.
I'd prefer to leave the Close button available, contingent on being in balance, because there's always a last record. Because of an AutoNumber field, it's important not to start/waste a record.
Thus, adding code that EITHER goes to next record OR Closes the record would be Brilliant.
Thanks, again. Pete

 

[wilpeter]

I've been trying the Macro approach with the following code (which presently gives an error about an argument not being optional):

If Me.OverUnder <> 0 Then
MsgBox "Out of Balance"
DoCmd.CancelEvent
Else DoCmd.Close acForm, "FrmReceipts", acSaveYes
DoCmd.OpenForm, "FrmReceipts", acNormal, , acFormAdd, acWindowNormal
EndIf

The problem is I don't have a manual and have to keep trying all permutations.
Any suggestions? BTW, it looks as if Federer has broken Murray in the third set. Hard to watch and compute. Cheers.

 

[wilpeter]

OK, Got it. Too many commas.

DoCmd.OpenForm "FrmReceipts",acNormal,,,acFormAdd,acWindowNormal

was the last line. It works now. Message displays if out of balance and when the message exits, the record remains. When corrected, the button closes and reopens a fresh form in Add Mode. The big advantage is the person cannot return to the entered transaction after it has closed (Shift Tab can't take you there.)
There is no solution to the Close button, because I have to be able to close the form eventually. If I use the menu to "File/Close" the record will automatically be saved, right or wrong, so it still isn't perfect.

Anyway, Thanks.

 

[Bryan]

If I use the menu to "File/Close" the record will automatically be saved, right or wrong, so it still isn't perfect.

Anyway, Thanks.

Did you try trapping this with the form's Before Update property as JBB suggested? ICBW, but I don't think that simply populating a field on the form is going to trip this just because all the fields have not yet been populated.

 

[wilpeter]

I learned a lot today, thanks to you and despite myself.
Using the Form's Before Update meets the other half of the problem, but won't allow the macro button procedure to exist (because it includes a command to Close). So I have to choose between (A)
Estopping the Close button from exiting the record with an imbalance; which also stops the user from tabbing to a new record from the form's last field.
or (B)
Clicking (or tabbing into) the Macro button (got focus) to stop exiting the record with an imbalance; and which closes the record and opens a new one...thereby prohibiting a saved record from being revisited (another auditor requirement).
Method (A) either cancels the record with the option of saving; or requires visiting the highest-numbered Tab Order field to reach a new record. It means that after the record is posted and printed it is vulnerable to change. If cancelled without saving, the AutoNumbered key field of the record is missing (another auditor's nightmare).
Method (B) makes the record vulnerable to Close (X) or "File/Close" which automatically saves the record that is out of balance.
I'll need to do some accounting consulting next.
Again, it has been a blast.