table level validation

[Guest]

is there a way to use multiple validation rules in table's properties that
would return a unique message depending on which rule was violated as data is
entered into form?

eg. rule #1: [exitdate] > [entrydate]
message "exit date must be later than entry date"

rule#2: [apptdate] > [inquirydate]
message: "The appt date must be later than Inquiry Date"


or must all this validation be done during form design for each control?

thanks for the help.

patti

 

[Jeff Boyce]

Patti

While there are ways to add constraints to tables (you have to work in SQL
statements, not the user interface), I'm not aware of a way to also
customize the error/validation messages.

Is there a reason why adding the validation tests and messages are
problematic in forms?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

or must all this validation be done during form design for each control?

You can use the Form's BeforeUpdate event, and check as many controls as you
like.

John W. Vinson [MVP]

 

[Guest]

thanks for the quick replies.


no problem doing it in forms ... was hoping there was a quicker & more
centralized way to do validation.

 

[Michael Gramelspacher]

thanks for the quick replies.


no problem doing it in forms ... was hoping there was a quicker & more
centralized way to do validation.

Based on all that I have read, business rules should be in table
constraints, if at all possible. From what I can gather the prevailing
consensus in the Access newsgroups is not to use any feature that is not
supported in query design mode. Therefore, some things that could be
handled in check constraints is done in form events. Personally, I do not
see why both check constraints and form events cannot be used together.
The Access check constraint is more powerful that SQL Server's, in that
Access allows subqueries and references to other tables.
Take this as just my opinion. One thing though, if you test the table
below, you will see that the error messages generated are not too awful.

Sub CreateTables()
With CurrentProject.Connection

.Execute _
"CREATE TABLE Table1" & _
" (Table1_id TEXT (10) NOT NULL PRIMARY KEY," & _
" entrydate DATETIME NOT NULL," & _
" exitdate DATETIME NOT NULL," & _
" inquirydate DATETIME NOT NULL," & _
" apptdate DATETIME NOT NULL);"

.Execute _
"ALTER TABLE Table1 ADD CONSTRAINT" & _
" ck_exit_date_must_be_later_than_entry_date" & _
" CHECK (exitdate > entrydate);"

.Execute _
"ALTER TABLE Table1 ADD CONSTRAINT" & _
" ck_appt_date_must_be_later_than_inquiry_date" & _
" CHECK (apptdate > inquirydate);"

End With
End Sub

Sub DropConstraints()
With CurrentProject.Connection

.Execute _
"ALTER TABLE Table1 DROP CONSTRAINT" & _
" ck_exit_date_must_be_later_than_entry_date;"

.Execute _
"ALTER TABLE Table1 DROP CONSTRAINT" & _
" ck_appt_date_must_be_later_than_inquiry_date;"

End With
End Sub

 

[Jeff Boyce]

Michael

Out of curiosity (since I am one of the folks who tend to use the UI first),
how do you document and check on the Constraints if you embed them within
the table definition?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Michael Gramelspacher]

Michael

Out of curiosity (since I am one of the folks who tend to use the UI first),
how do you document and check on the Constraints if you embed them within
the table definition?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
I think you are asking how anyone can even see them or know there are
there. You cannot in the sense that table design view is the
documentation. Would it be fair to ask how you document and check the
constraints if you embed tham in the code? What I do is create a module
containing all the create and alter table statements as above. I guess I
am saying that I consider DDL the documentation of a table. In truth I have
no real world experience and cannot say for certain that DDL is considered
table documentation.

 

[Jeff Boyce]

Michael

Thanks for the clarification. I can see that by putting the creation code
in a module, there's a place someone (other than the originator) can go to
see what Constraints were (supposedly) applied.

I say "supposedly" because it is not clear yet how to tell if the
Constraints still apply to what I'm looking at right now, or if other
Constraints (not documented anywhere) were applied, or which Constraints may
have been altered or removed (but not documented).

I'll offer the notion that having validation handled in the forms' code
explicitly demonstrates what business rules are being applied, rather than
having implied business rules (via your modules) that may be more difficult
to confirm.

Thanks for the stimulating discussion!

Regards

Jeff Boyce
Microsoft Office/Access MVP