Please Help!!!!!!!!!!!!!!!

[george.vniekerk]

How can i prevent a record from saving unless a calculation in an unbound field matches that of a bound field?

[RuralGuy]

Use the BeforeUpdate event to validate and set Cancel = True and display a message if you don't want to save.

[george.vniekerk]

Thanks for the response RuralGuy.

Do i insert the code on the Forms "after update" event and please give me some direction as to the string that i need to use.

I basically have 6 fields. All fields are manually updated with values. All 6 fields are bound fields and updates a table.

The 7th field is unbound and i have written a formula to calculate whether field 6 equates to field 1 less fields 2 to 5.

If it matches, the unbound field 7 shows a message on screen to highlight the descrepancy.

The string in the Event can probably use the result of the unbound field 7??????

[RuralGuy]

You want the *BeforeUpdate* event, not the AfterUpdate event! Just do the same calculation and set Cancel = True if it fails.

[george.vniekerk]

sorry about that. Not too familiar on how to start the string. would it be possible to give me an example?? Thanks

[RuralGuy]

What does your formula look like?

[RuralGuy]

Go ahead and post what you use for your formula.

[george.vniekerk]

Herewith the formula used in the unbopund field:

=IIf([PerStatement]-([OS Payments]+[OS Discounts]+[OS Invoices]+[Other Items])=[Per Month End TB]," ","Per Month End TB Item does not balance Please check entered value")

[RuralGuy]

In which case your BeforeUpdate event should look like:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If [PerStatement] - [OS Payments] + [OS Discounts] + [OS Invoices] + [Other Items] <> [Per Month End TB] Then
      MsgBox "Per Month End TB Item does not balance Please check entered value", vbCritical + vbOKOnly
      Cancel = True
   End If
End Sub

[george.vniekerk]

Thank you RuralGuy!!! Your code works like a charm. Much appreciated!

Just two more questions.

1). How do i add another formula in the same string based on the following set of fields? I have converted into the same format, however am not sure how to combine. Do i perhaps use And?

If [Other Not Yet Due] - ([OS Inv Bfore Cut Off] + [GRN TB] + [GRN VAT] + [Other Risk Items]) <> [Potential Risk] Then
MsgBox "Potential Risk Item does not balance Please check entered value", vbCritical + vbOKOnly
Cancel = True

2). I have a Main Form and Subform. In the Main Form, i have a Customer Name field. I want to automatically update the Customer Name field in the sub form with the value of the Customer Name field in the Main Form. I have used the following code in the field's After Update Event to do a similar thing, however this was from one field in the Sub Form to another field in the Sub Form and it worked perfect. Not to sure how to do from a Main Form to a Sub Form though?

Me.SupplierKey.Value = Me.AccLink.Value & Me.StatementDate.Value

Thank you once again for the assistance!

Regards
George

[RuralGuy]

I would probably put them in series:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If [PerStatement] - [OS Payments] + [OS Discounts] + [OS Invoices] + [Other Items] <> [Per Month End TB] Then
      MsgBox "Per Month End TB Item does not balance Please check entered value", vbCritical + vbOKOnly
      Cancel = True
   Else
      If [Other Not Yet Due] - ([OS Inv Bfore Cut Off] + [GRN TB] + [GRN VAT] + [Other Risk Items]) <> [Potential Risk] Then
         MsgBox "Potential Risk Item does not balance Please check entered value", vbCritical + vbOKOnly
         Cancel = True
      End If
   End If
End Sub

...and here's a link for your favorites: http://access.mvps.org/access/forms/frm0031.htm