Well, you can use some of the built in functions and events.
The problem here is that you have some theory, or idea that using the built
in JET validation is going to save you some code, and it is not. You have to
get rid of
this imaginary idea that you have.
The reason is as follows::
While you can have many fields required, and the JET engine can thus trigger
an error, that error code will simply tell you that you a have a required
field missing. What good is that? Your code will still have to check which
field is missing, and give the user a intelligent message. I mean trapping
the error that field is missing, and the giving the user a message such as:
A require field is missing.
The above is useless, and really NO BETTER then the error that you get from
JET.
To make a an intelligent message, YOU WILL need to check which field it was
that triggered the error message. Really, the message you need is:
The field "LastName" is required
Further, if you are not trying to torture the hell out of your users, after
they click ok to the above nice message, your cursor should be placed in
that last name field (assuming that this field in actually displayed on the
input form). So, really, you still need to code/write a nice custom message.
Further, you should take different action for some fields that are required.
Minimally, I usually add error trapping code to the Save button's On
Click
Well, using a save button is really the problem here also. I don't know why
people insist on having save buttons!, but they are really dumb, and not
necessary!. Lets leave this issue of the save button for another day, but
when a user moves to another record, do you think they want to save? Thank
goodness thinks like cars were made before software developers came along. I
can just see it now: you turn the key in the car, and it asks you do you
want to start the car? Or, how about a file cabinet, you open it, place a
letter inside, and then when close the file cabinet, it will ask you do you
want to save your letter? (how dumb can we get!). Ms-access has NOT have a
save button for 10 YEARS NOW! More and more of the software industry is
finally realizing that save buttons are really a bad idea. I always been
tortured when exiting Word, and it asks me do you really want to save? (how
I wish it was like ms-access!). You MOST certainly should offer a user a
un-do button. But to bother millions of users around the world daily is not
needed.
The above is especially more so when using bound forms. The reason is that
you need to save the parent record to disk before it is possible or even
feasible to start adding child records. In fact, this rule applies to ANY
major database. You can't solve, or approach this problem any other way. In
other words, if you are adding child records to a data base, you MUST first
add the parent record, and have the correspond key generated for the child
records to use. I would not fight this fact.
I don't think is copes well with the user clicking a button that moves
the focus to a subform in code, which forces a save
Yea, boy!....you got that one right! How can you possibly let the user add
child records without first saving the parent record? Further, are you going
to train your users to hit the save button BEFORE they move to the sub form.
(this is now starting to get really silly here!).
Even one of the original authors of VB has whole cheaper in a book devoted
as how dumb it is to have users to save. And, look at the success of
products like the palm pilot (again, just like ms-access, they used implied
saves). Anyway, thank goodness those ms-access developers for the last 10
years have had this concept correct. More and more software is now working
this way.
The only event that would seem to cover all the bases is the form before
update, which has a cancel event. But to use it I have to "know" which
fields are required and should not be dupes, and test in code to see if
the rec is safe to update.
That is correct. However, what kind of error messages, and user friendly
kind of code are you gong to use?
Answer: you will in fact have to deal with the required fields, and the dup
fields anyway!
Further, you will want to give the user a intelligent message, and deal with
it in a intelligent fashion. The amount of code to check for blank fields is
trivial, and in ADDITION THIS MEANS YOU can give the user a friendly
message. Writing the verifying code in the before update ALSO MEANS that you
can take appropriate action. Thus, you can do things like after the user
clicks ok, you can move the cursor to the bad field.
I mean, what do you want with the errors from the JET engine? So what if JET
spits out a error message. You still need to do something here! You going to
give the user a list of all the fields at once! You don't plan to generate a
list of the offending fields that jet found and then give the user a whole
big huge message?
I mean, lumping all that field information, and how you want to deal with it
simply means you will need SOME CODE for each of those fields. That code
will have user friendly messages, and even the possibility of moving the
cursor/focus to the offending field for the user.
What good is dealing with an error message from JET when you have to then
deal with the problem?
I would suggest you just integrate the checking code along with the nice
user friendly code that you WILL NEED write anyway! Since any good developer
is going to make user friendly messages, and take user friendly action, then
the addition of one line of code to "check" for the blank field is not a
development penalty at all. Your idea of saving a bunch of code by using the
engine level here is simply not applicable all. I see no real way to save
any code EVEN IF you could get the engine to tell you more about the error.
So, we get in the before update:
' check for last name
if isnull(lastName) = True then
Cancel = true
msgbox "LastName is a required field",vbExclamation,"Required Field"
me.LastName.SetFocus
exit sub
endif
As you can see, the above code gives the user a nice message (which you have
to give anyway). The above code also puts the cursor right into the field
that caused the error message (which you should do). So, how many lines of
code does getting a return error from the data engine save you in the above?
It saves you bunkus lines of code. I mean, if you go and add a few new
fields that are required, and don't write any code to deal with those new
fields, then your users will get that weird system error. Even if you could
trap that system error, you will still have to code, and give a nice error
message for each field. As it stands, I see nothing in your weird search for
a solution that is going to save you any coding at all.
You most certainly can, and should perhaps wire a few nice re-useable
routines. For example, if I have 5 controls on the form bound to 5 required
fields, then I certainly could using something like:
cancel = True
if BadCheck("LastName","Last Name Field is required") then exit sub
if BadCheck "Company","Company Name Field is required" then exit sub
et.c etc.
' if we get here...every thing ok...
cancel = False
The code in BadCheck could be:
Private Function MyCheck(strField as string,strErMsg as string) as boolean
BadCheck = False
if isnull me(strField) = True then
BadCheck = True
msgbox strErMsg,vbExclamation,"Required Field"
me(strField).SetFocus
endif
end Function
So, you can use some creative coding to make up a library of routines. So,
the above could be use for required, and you can cook up a few more for
duplicates etc. And, if you look closely at the above, note how the controls
on a form can be referenced by the value of a variable. This means you could
even put that list of fields, and the error messages in a table, and have a
generic code "loop" through that list.
I don't know where, or why, or what your mental block is resenting the use
of the before update event, but it is a far superior event model then what
you have withy VB or c++ forms for example (well, in fact those systems
don't EVEN have the before update event, so you have to write EVEN MORE!. In
fact you are dealing with un-bound forms in those systems anyway. Of course,
using VB, and un-bound-und forms means a $12,000 project in ms-access will
cost AT LEAST $35,000 or more in VB. The 3 x productive you get with
ms-access forms is a conservative estimate by most experienced developers.
But isn't there a way to
gracefully handle this with the built in stuff Access offers?
If you mean by graceful using the way cool built in events like the before
update, and giving the user a nice friendly message, then the above
solutions beats the pants off of just about every development platform I
have seen, or used. And, I assure I have used a LOT of platforms....
And, yes, you could also cook up a class object to help with the above, but
that class object will still be fired, and controlled in the before update
event. You might even as I mentioned, make a table, and generic routine.
If you are going to deal with your users via the UI, then use the UI. The
data engine will not help much here at all. And, I don't know of a
development platform where the engine helps that much anyway.
Further, if you tie all your code too tight to that one data engine, what
happens when you want to move the data to sql server, or some other data
engine, and KEEP your front end in ms-access? Ms-access makes a great front
end to sql-server, or oracle, or many of the other systems out there.
Ms-access is not a database, but VB UI development system. You might use
the JET data engine, or perhaps you need 1000 users with ms-access? You then
would use sql server with ms-access. So, ms-access is the UI development
too. Placing your user friendly code in the UI makes sense. Save the engine
checking for your coding, and catching error in processing code/routines
that you may write (but that engine stuff is just not made, nor used by the
users).
All in all, I don't see any other viable, and reasoned out approach that
will be more efficient, and take less developer time then the above.
If have not yet used class objects in ms-access, you can read the following
article of mine:
http://www.attcanada.net/~kallal.msn/Articles/WhyClass.html