Setting Global ValidationRule(s)

  • Thread starter Thread starter Charles L. Phillips
  • Start date Start date
C

Charles L. Phillips

Hello,
Is there a way to apply a "ValidationRule" property globally?

Example:
I have five (5) tables, each having a "telephone number" field.
Is there a way to set the "ValidationRule" property once (globally) for ALL 5 tables???


Charles L. Phillips
 
NO HTML POSTS PLEASE! Many people dislike them intensely & will not answer
them. For one reason, opening an HTML posts exposes the reader to various
HTML-only exploits.

There's no way to do what you want, directly in the user interface. But you
could write some code to do it programatically. For example, the following
(UNTESTED) code applies the nonsense validation rule 1=0 to any field called
"blah" in any table:

dim db as database, td as tabledef, fld as field
set db = currentdb()
for each td in db.tabledefs
for each fld in td.fields
if fld.name = "blah" then
on error resume next
fld.properties.delete "ValidationRule"
on error goto 0
fld.properties.append _
fld.createproperty ("ValidationRule", dbText, "1=0")
endif
next
next
set db = nothing

I >have not< tested that code, so you may need to tweak it. But the basic
idea is AOK. You should probably omit tables ith names like "MSys*" or
"USys*". And, to be really nifty, you could mabe store the relevant table &
field names & validaion rules & messages >in a table<, and drive the process
from that table. Then, none of the relevant table or field names (or
validation rules or messages) are hard-coded in the VBA code.

HTH,
TC


Hello,
Is there a way to apply a "ValidationRule" property globally?

Example:
I have five (5) tables, each having a "telephone number" field.
Is there a way to set the "ValidationRule" property once (globally) for
ALL 5 tables???


Charles L. Phillips
 
Charles

I'm having a little trouble wrapping my head around a situation that needs
the same field (telephone number) in five tables. It may be that, with
further normalization, your database would only need a single validation
rule. If you post more info about your data structure, the 'group readers
could offer alternate approaches.
 
While TC's solutions is certainly effective, the first question is why
do you have 5 tables with a phone number field?!?

You should look at how normalized your data structures are. You may
really only need one table with phone numbers, then the validation rule
will, by default, be global to all phone numbers.
 
TC said:
Oops! Saw the trees - missed the wood!
Click to expand...

I wouldn't be too sorry: what about

Patients(*PatientID, ..., HomePhone, ...)

Specialists(*DoctorID, ..., DirectDialNumber, ...)

InsuranceCompanies(*ICCode, ..., PhoneNumberForClaims, ...)

Laboratories(*LabName, ..., PhoneNum, ...)

Buildings(*BldngCode, ..., *JanitorPhone, ...)

ClinicalRooms(*BldngCode, *RoomNumber, ..., DirectLineNumber, ...)

FamilyPractitioners(*PracticeCode, ..., OfficeNumber, ...)

You could make a case for subclassing FamilyPractitioners and Specialists,
but in many situations they would be thoroughly different entities.

Remember too that most real databases will allow the developer to create
new datatypes with their own constraints and defaults, so it would be
possible just to add

ALTER TABLE Something
ADD COLUMN RingThisInEmergencies PHONENUMBER NULL;

and have it all done for you.

Mind you, I was more intrigued by what the OP might want to put in a
validation rule for a phone number. To me it seems like hard work than
benefit...


B Wishes



Tim F
 
Mind you, I was more intrigued by what the OP might want to put in a
validation rule for a phone number.
Click to expand...

Yes, good point! I normally pride myself on being able to see through the
"stated" question, to the "real" questions. Not this time, I fear :-(

Cheers,
TC
 
Tim

Perhaps just a difference of approach. If I had multiple tables that each
required a phone number, I'd use a phone number table and the primary key
from THAT table as a foreign key in each of my tables.

Regards

Jeff Boyce
<Access MVP>
 
TC said:
Yes, good point! I normally pride myself on being able to see through the
"stated" question, to the "real" questions. Not this time, I fear :-(
Click to expand...

I sometimes feel that the main ambition for us responders here is to work
out the question we _can_ answer, rather than the one the poster posted!
Being in medicine is great training for that:

- "Doctor, doctor, I have a pain in my leg."
- "Alright, now about your blood pressure..."

All the best :-)


Tim F
 
I'd use a phone number table and the primary key
from THAT table as a foreign key in each of my tables.
Click to expand...
I'd buy that if the phone numbers had some kind of structure: Country,
Area, Exchange, Type, Number, Extension, HasToneDialling, CanAcceptFaxes,
and so on. In the generality, if it's just a text string, I'd go for just
keeping the numbers.

We don't often see a table of ChristianNames and FKs into that one. And
they probably "repeat" more often than phone numbers!!

<http://www.hyperdictionary.com/search.aspx?define=devil's+advocate>


All the best


Tim F
 
Back
Top