Validation Rule

R

Robert Mark Bram

Hi All!

I have two simple tables in access 200:

ACCOUNT
- id (primary key)
- balance

WITHDRAW
- id (primary key)
- accountId (foreign key from ACCOUNT)
- amount

I have not created any forms yet. Instead I want to embed the logic of
WITHDRAW.amount<=ACCOUNT.balance in my tables through Validation Rules.

Can I do this? I ask this question because I cannot see a way to use the
Expression Builder to make a query and test the results..

Any advice would be most welcome!

Rob
:)
 
D

Dave

As far as I know, validation rules apply only within one table, and if you
want to use other field values for comparison, you have to use the
table-level rule rather than the field-level one. To do what you want, I
think you're going to have to write a little code behind a form.
 
A

Allen Browne

No, do not store the Balance in the ACCOUNT table! A very important, basic
normaliztion rule says not to store dependent data.

How about these tables:
ACCOUNT table:
- AccountID primary key
- AccountName, etc.

TRANSACTION table:
-TransactionID primary key
- AccountID foreign key
-TransactionDate date/time
- Multiplier 1 for deposit; -1 for withdrawal
- Amount Currency. amount of deposit/withdrawal.

The balance for an account is:
DSum("[Multiplier] * [Amount]", "Transaction", "AccountID = 1")
and unlike the stored balance, that can never be wrong.

You cannot use a validation rule at engine level, and probably should not
anyway, because you may want to allow for overrides and permit an account to
overdraw (e.g. so you can charge the client for a fee.) Use the BeforeUpdate
event procedure of your form to calculate the balance, and block the
transaction if it overdraws the account:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim curResult As Currency
If (Me.Multiplier = 1) Or (Me.Amount.Value <= Me.Amount.OldValue) Then
'do nothing: a deposit, or reduced/unchanged withdrawal amount.
Else
strWhere = "(AccountID = " & Me.AccountID & ")"
If Not Me.NewRecord Then 'exclude this transaction.
strWhere = strWhere & " AND (TransactionID <> " &
Me.TransactionID & ")"
End If
'Look up the total, and add this transaction.
curResult = Nz(DSum("[Multiplier] * [Amount]", "Transaction",
strWhere),0) + (Me.Multiplier * Me.Amount)
If curResult < 0 Then
Cancel = True
MsgBox "This could overdraw you by " & Format(curResult,
"Currency"), vbExclamation, "Transaction blocked"
End If
End If
End Sub

Notes:
1. Strictly speaking the separate Multiplier field is not needed, but it
makes the interface very easy, and allows you to easily do simple things
like separate subforms for deposits and withdrawals, and complex things like
reversals that are treated as special cases.

2. You proably want to mark all the fields in the Tranasaction table as
Required, and set a Validation Rule on Multiplier of:
1 Or -1
so it cannot be anything else.
 
R

Robert Mark Bram

Thank you for your time and detail Allen,

I will make much use of your help! :)

Rob
:)
 
Top