Parameterize validation rule and text?

[Laurel]

I have an application which is highly parameterized. That is, values in a
table drive a lot of what's going on, such as "maximum score." The one
thing that seems to require hands-on non "dumb user" manipulation is the
validation in the tables themselves. If I change the value of "maximum
score" from 5 to 3, for instance, then for each of the Score1, Score2, and
Score3 rows in the tblScores table, I have to change the validation rule
from "<=5" to "<=3", and the validation text from "The score must be <= 5 or
null." to "The score must be <=3 or null." Is there any way around this?

TIA
LAS

 

[Wayne-I-M]

Hi Laurel

It's just an idea as I can't see your database so may not work. But if it
were me I was not refer to a specific number in the criteria I would refer to
the field/control holding the number. This way if you change the criteria
you will not not have to alter the formula.

Like this <=[FieldName]

Also as you may decide now and again just to have a look at different
combinations you could add an unbound control to the form and use the
AfterUpdate to check for Nul in this box and if you have added a number then
.........

Something like ...If Me.UnboundControl <> Null then <=[FieldName] etc etc etc

Hope this helps

 

[John Nurick]

Hi Laurel,

It's possible to do this sort of thing by adding check constraints to
the database. Most Access users don't use them, partly because you can
only manipulate them via SQL and not in table design. I believe you need
to switch the database to use "SQL Server compatible syntax" (in
Tools|Tables and Queries).

In my test database I set up tblConstraints with two fields,
C_Label* - text(20)
C_Value - number (long)
and added one record
Max_Score, 5

I have a field called Score in the table tblRon. Executing these two SQL
DDL statements (create a new query, switch to SQL view, type the
statement and run the query) added two constraints.

The first, "Exceeds_maximum_score", prevents you from entering a value
into tblRon.Score that is greater than the Max_score value stored in
tblConstraints. The second excludes scores below zero.

ALTER TABLE tblRON
ADD CONSTRAINT Exceeds_maximum_score CHECK (
Score <= (
SELECT C_Value FROM tblConstraints
WHERE C_Label = 'Max_score'
)
)
;

ALTER TABLE tblRON
ADD CONSTRAINT Score_cannot_be_less_than_zero CHECK (
Score >= 0
)
;


Having read this far you know about as much about check constraints as I
do. I suggest you try them out on a test database, and read up on them.
If you use Google Groups to search for something like this:

group:microsoft.public.access.tablesdbdesign collins constraint

you'll find posts by Jamie Collins who really understands this area.
Probably there's documentation at msdn.microsoft.com, but I haven't yet
got round to searching for it.

Good luck!

 

[John Nurick]

FWIW I have experienced a few issues with this 'correlated' construct
to the point where I don't trust the engine to use the correct value
for 'Score' when an update affects more than one row. Also, such a
construct is slightly more challenging to write and debug. I prefer an
'assertion' type construct e.g. (aircode)

ALTER TABLE tblRON ADD
CONSTRAINT Exceeds_maximum_score
CHECK (
NOT EXISTS (
SELECT *
FROM tblConstraints AS C1, tblRON AS I1
WHERE I1.Score > C1.'Max_score'
)
)
;

To debug: drop the constraint, add some 'bad' data, run the query

SELECT *
FROM tblConstraints AS C1, tblRON AS I1
WHERE I1.Score > C1.'Max_score'

Thanks for chipping in, Jamie. This is an area I know little about but
it seemed an obvious solution to the OP's problem. But...

1) I'm not sure what you mean by C1.'Max_score' in the WHERE clause and
what's reason for the Cartesian join. ISTM that this does the job:

ALTER TABLE tblRON ADD
CONSTRAINT Exceeds_maximum_score
CHECK (
NOT EXISTS (
SELECT *
FROM tblRON
WHERE Score > (SELECT C_Value FROM tblConstraints
WHERE C_Label = 'Max_Score')
)
)
;

2) How would one modify this constraint to allow Null scores?












I think I see that the Cartesian join ensures that the value of Score is
always checked in all records