Advice/Opinions About Validation Best Practices?

C

CS

I am a beginner with some questions about best practices re: data
validation.

Currently, I am handling all absolutely required information validation at
the Table design level for certain "must-have" fields, using Is Not Null,
and generating validation errors at the table level (like "You must enter a
date for this event.").

At the form level, I am using combos to control accurate input from the
users in critical places (we will have a very small group of users with whom
I will have much contact and oversight). These combos are generally tied to
fields that are Is Not Null at the table design level, forcing the user to
make a selection before saving the record, and generating a custom msgbox
indicating the missing field that must be filled.

Because I am a beginner, I would like to know now if there are any hidden
pitfalls to validating data at the table level like this, or if someone
could point me to a "best practices" basic information anywhere on
validation in general.

I also have two specific validation questions:
1. I have a couple of tables where I have the PK and a foreign key that
must not be a duplicate (set indexed, no dups at table design level
currently). For instance, a contact must have more than one membership, so
there is the MemberID (PK) and ContactID(fk - indexed, no dups) in the
tblMember. Violation of this generates the ugly Access generic msgbx "The
changes you requested to the table . . . ". Can I sustitute my own msgbox
for this without VBA?

2. Can I have more than one msgbox for a single field or control (for
example -- a bad date brings up one msgbox, no date brings up another?), and
if so, where would I do that (table level, control level, macro, VBA)?

Be gentle with me. I am just figuring this out, and though I plan to dive
into VBA very soon (the manuals are on my desk), I am still a virtual VBA
virgin. I am aware that I will have to "go there" (VBA), and probably very
soon -- if you are of the opinion that the time for me to leap is now,
please let me know.

Thanks in advance for any help,
Carol
 
S

Steve Schapel

Carol,

What you have done as regards the table-level validation of the fields
where an entry is required, is good. I know of no "pitfalls" with this
approach. The Validation Rule only kicks in at the point where the
record is being saved, so circumsatnes where I would not use this
approach include:
- if I wanted the record to be saved before all data was entered
- if I wanted to validate data in certain fields at some point in the
process prior to the data being saved

Regarding your specific questions:
1. I do not know of any way to customise this apart from using a macro
or VBA procedure. It is difficult to be specific, as I am not really
clear about your explanation. Did you mean "a contact must *not* have
more than one membership"?
2. Yes. VBA code might look something like this...
If IsNull(Me.YourDate) Then
MsgBox "Date required"
ElseIf Me.YourDate Not Beween #1/01/2005# And Date Then
MsgBox "Invalid date"
End If

Most people start using VBA when they need to do something that they
can't do without VBA :)
 
C

CS

Thanks so much, Steve -- this was exactly the kind of info I was looking
for, especially the "if I wanted . . ." exceptions to validation schemes,
and the code suggestion, which looks like a simple beginning for a VBA
beginner like me.

You are quite right that I meant "contact must NOT have", instead of must
have -- and I suspected that the answer to this question was VBA -- ah
well -- (trudging off to the three large books waiting on the desk) -- time
for a bit of manual immersion, I guess.

Thanks again for your help.
Carol
 
S

Steve Schapel

Carol,

Please don't ask that question... some people might feel compelled to
answer, and so on. ;-)
 
Top