Validation Rules in the Forms

[JoeyHenry]

Hi,

I am new to access and VBA. I need to have a validation rule after the user hit "enter" on one my forms. The purpose of this database is to record claims. Please see attachment for further visual on how I designed the database.

Background:

1. There are three main tables and forms: DTASA, CMRF, CM
2. For one DTASA there could be claims (CMRF). For one claim, there could be many credit memos (CM).

Validation Rule:

1. After the enduser enters the amount in the CMRF form, access should then validate whether the claims (both previous and current) is greater than the DTASA amount.
2. If the sum of the CMRF is greater than the DTASA amount, an error message should alert the enduser "Claims greater than DTASA".
3. If the sum of the CMRF is lesser than the DTASA amount, the end user should be able to continue the process.

Please help how we can code this in VBA or some other easier techniques for this validation rule.

Regards,
Joey

[June7]

In the Amount textbox BeforeUpdate event Something like:

If DSum("Amount", "tablename", "CMRF=" & Me.CMRF_ID) + Me.Amount > Me.DTASA_ID Then
MsgBox "Claims greater than DTASA"
Cancel = True
End If