table column level validation

D

Dale Fye

Personally,

I prefer to put this kind of logic in the application, rather than in the
tables. You can use a forms BeforeUpdate event to check that values are
appropriate, and if not, you can display a meaningful error message and then
cancel the update.

When you put the logic in the columns properties, you generally get an error
message that is very generic.

HTH
Dale
 
J

John W. Vinson

i would like to know, how/where to put code/and if not possible why please!
Click to expand...

You can't do this in a Table, but you can use the Form's BeforeUpdate event to
detect this condition. I'm not at all sure what you're summing over but you
should be able to use DSum() in the Form's event.

One possible problem if you're using a form and subform is that the main form
record is saved to disk when you first set focus to the subform, and each
subform record is likewise saved to disk when you move to the next - so a
BeforeUpdate event may cause an apparent error if the withdrawals are
processed before the deposits. You may need to store the "active" transactions
in a temporary table, and only move them to the actual table when complete and
validated.

John W. Vinson [MVP]
 
R

RoyVidar

John W. Vinson said:
You can't do this in a Table,
Click to expand...

If one are willing to use methods that aren't directly supported within
the Access interface, and one needs engine level validation - for
instance if this might be updated from outside Access, I think this
should be doable through CHECK CONSTRAINTS.

CurrentProject.Connection.Execute "ALTER TABLE banktrans " & _
"ADD CONSTRAINT sum_deposit_less_than_sum_withdraw " & _
"CHECK (0<=(SELECT Sum(deposit)-Sum(withdraw) FROM banktrans))"

Though, one might probably need some other identifier (some ID?)
to determine correct account, customer...

More info for instance here
http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx
 
Top