Validation Rules

[Guest]

1. Can you find out what the validation rules are as set in a tables
definition (e.g. required=yes) are in VB code? I'm trying to design a
database so that the field validations are all in one place rather than have
some in the tables and some in the VB code. Would I be better off writing
validation procedures instead and just calling these?
2. I have a mandatory field but there is no sensible default so it starts
off as a null value. Whats the best way of handling this? I can't use
before_update as it only fires if the user visits and changes the field -
equally on_exit etc is no good as the user can avoid entering the field. I've
tried using a rule in the tables and trapping the form_error but this seems
perverse as I'd like to handle everything in the same way? Any ideas on how
to make it all neat and tidy much appreciated.

 

[Arvin Meyer [MVP]]

Answers in line:

1. Can you find out what the validation rules are as set in a tables
definition (e.g. required=yes) are in VB code? I'm trying to design a
database so that the field validations are all in one place rather than
have
some in the tables and some in the VB code. Would I be better off writing
validation procedures instead and just calling these?

Try using the Before Update event for all your validation. You can use it at
the control level or at the form level. If you really want all your rules in
1 place, you could put them in a table and grab them in the control or form
update event. I just use the event itself, and have never considered
bothering looking them up from a table.

2. I have a mandatory field but there is no sensible default so it starts
off as a null value. Whats the best way of handling this? I can't use
before_update as it only fires if the user visits and changes the field -
equally on_exit etc is no good as the user can avoid entering the field.
I've
tried using a rule in the tables and trapping the form_error but this
seems
perverse as I'd like to handle everything in the same way? Any ideas on
how
to make it all neat and tidy much appreciated.

You can validate anything on the form in the form's Before Update event.
FWIW, I'd use both the control and the form. It is far more user friendly to
validate at the control level, but, as you have noticed, you must visit that
control to do the validation. Having the form as the backup validation
source makes sure it gets done.

 

[onedaywhen]

I'm trying to design a
database so that the field validations are all in one place rather than have
some in the tables and some in the VB code.

See:
Mop the Floor and Fix the Leak
by Joe Celko
http://www.dbazine.com/ofinterest/oi-articles/celko26/

"In part one of this article, we talked about how it is absurd to put
all of the data integrity, verification, and validation rules in the
front end application programs, so we would not have to bother with
those constraints on the server side.
"The converse of this would be to put the constraints only on the
database. This approach will actually work better than the first one!
Every front-end program will be tested against an identical set of
constraints, and will get an identical set of error messages back. This
is good and desirable. So, what is wrong with it...? "

Jamie.

--

 

[Guest]

Sorry for the lateness in saying so but thanks for the reply. The number of
options/events in Access was confusing the h*ll out of me - as was the soddin
automatic updates. However, I'll follow your advice which seems reasonable.
Are there and really good sample pieces of code for standard things like
mandatory fields, cross field validations, handling referential integrity etc
?

 

[Guest]

Hi, Thanks for pointing out that there is an alternative opinion. I'm going
to have to experiment and make my mind up. Cheers

 

[Arvin Meyer [MVP]]

Sorry for the lateness in saying so but thanks for the reply. The number
of
options/events in Access was confusing the h*ll out of me - as was the
soddin
automatic updates. However, I'll follow your advice which seems
reasonable.
Are there and really good sample pieces of code for standard things like
mandatory fields, cross field validations, handling referential integrity
etc
?

Not specific ones that I am aware of (although I am nowhere near aware of
everything). For mandatory fields just add:

Is Not Null

to the Validation property of the control, and have the BeforeUpdate event
check the controls for values, something like (aircode, without error
handling):

Sub Form_BeforeUpdate (Cancel As Integer)

If Len(Me.ControlName & vbNullString) = 0 Then
Cancel = True
MsgBox "Value required", vbOKOnly, "Data Missing"
Me.ControlName.SetFocus
End If

End Sub

Something similar for cross field validations, and for referential
integrity, use the database engine for enforcement.