Advice/Opinions About Validation Best Practices?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CS - 20 May 2005 17:36 GMT

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

Reply to this Message

Steve Schapel - 20 May 2005 23:20 GMT

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 :-)

Signature

Steve Schapel, Microsoft Access MVP

> I am a beginner with some questions about best practices re: data
> validation.
[quoted text clipped - 37 lines]
> Thanks in advance for any help,
> Carol

Reply to this Message

CS - 21 May 2005 00:40 GMT

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

<snipped>

> - 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
[quoted text clipped - 11 lines]
> MsgBox "Invalid date"
> End If

<snipped>
,<snipped> >.

> > I also have two specific validation questions:
> > 1. I have a couple of tables where I have the PK and a foreign key that
[quoted text clipped - 4 lines]
> > changes you requested to the table . . . ". Can I sustitute my own msgbox
> > for this without VBA?

Reply to this Message

CS - 21 May 2005 00:41 GMT

Also -- what is the accepted netiquette in this forum -- top post, or bottom
post?

Reply to this Message

Steve Schapel - 21 May 2005 00:49 GMT

Carol,

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

Signature

Steve Schapel, Microsoft Access MVP

> Also -- what is the accepted netiquette in this forum -- top post, or bottom
> post?

Reply to this Message