I think you are missing some approaches and solutions here.
The second level is the control by control (or field by field) error
checking. This goes into greater depth of error checking such as checking
to be within parameters. This is the level that I primarily have issues
with.
Well, right off the bat, you do realize that virtually MOST of the
conditions that you have on a text box by text box bases needs to also be
checked in the records before update. The reason for this is of course that
the user may skip around, and not change some data that is now incorrect, or
requited. The ONLY reliable place where you can do the field verification is
in the forms before update event. While it is nice to inform the user that a
field/text box is required, the user may in fact not even edit that text
box, so you MUST put the verifying code in the forms before update event.
Sure, some things like combo boxes, and date ranges should, and can be
checked as the user works there way through the form, but at the end of the
day, the verification for required fields etc. does need to be run in the
form before update event. If you don't keep this simply fact in mind, then
you will make a complete mess of you verification process.
Further, any good developer will actually make sub, or separate routine to
be run at form verification time (you done this..good!). As I will explain,
you will need to run the verification code in other places, or other times
then the before update event (a great example of this is if you have your
own save button, or in fact a delete button).
So, for example, I use the following code in the before update event to
force certain fields to be required. Note how this code is general, and be
re-sued for any form:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = MyVerify
End Sub
Now, you create a function called MyVerify, and place the records verify
code in that routine. Further, note that for each individual text box, you
might want to give the user a error message, but you do NOT want to use or
set the cancel in the before update event. That means the user gets a
message as they work through the form (this is only done if you MUST HAVE
messages during data entry..and not at the final save). And, if you MUST put
a cancel in a individual text box, then users MUST learn that edit->undo can
get them out of the field in those cases where you MUST force verifying at
the field level. Further, the same goes for deleting a detail line. If you
don't offer a custom delete option, then the users will again get stuck
(when they click on the record selector, a save is attempted, verify code
runs..and they are stuck!!). So, you MUST offer a custom delete option for
the continuous form, and allow the me.undo for the field. So, if a user
tries to click on the record selector when verifying complains, they will be
stuck. Simple solution: just give them a delete option, and your re-set
option in the menu bar. And, of course the un-do field edit also.
Note that the following example code 3 required fields, and I use a
collection object to simply coding (you pass the routine the field name, and
also a "text" message that the user will get if the field is required.
Private Function MyVerify() As Boolean
Dim colFields As New Collection
MyVerify = False
If bolNoVerify = True Then
Exit Function
End If
colFields.Add "cboVolunteerType,Volunteer type"
colFields.Add "LastName,Last name"
colFields.Add "FirstName,First name"
MyVerify = vfields(colFields)
If MyVerify = True Then
Exit Function
End If
' any additional verify code goes here
If IsNull(Me.cboVolunteerType) = False Then
If IsNull(Me.cboVolunteerType2) = False Then
If Me.cboVolunteerType = Me.cboVolunteerType2 Then
MsgBox "Volunteer '1' cannot be same as volunteer type 2",
vbExclamation, AppName
Me.cboVolunteerType2.SetFocus
MyVerify = True
Exit Function
End If
End If
End If
End Function
I also have a test for two combo boxes that cannot be the same.
And, here is the code for the "vFields". This code gives the nice error
message, and then places the focus/cursor into the offending field;
Private Function vfields(colFields As Collection) As Boolean
Dim strErrorText As String
Dim strControl As String
Dim I As Integer
vfields = False
For I = 1 To colFields.Count
strControl = Split(colFields(I), ",")(0)
strErrorText = Split(colFields(I), ",")(1)
If IsNull(Me(strControl)) = True Then
MsgBox strErrorText & " is required", vbExclamation, AppName
Me(strControl).SetFocus
vfields = True
Exit Function
End If
Next I
End Function
So, the above is how we cleanly handle forms, or record verification. Now
let look at your following issues:
As a general rule, you want to have all of your data validated to the
extent
possible. From the surface level, the BeforeUpdate Event looks good to
use
for second level of error checking.. However, if the user is wanting to
reset the form rather than continue with the updating, and the developer
of
the program developed a command button for such a thing on the form,
that's
where the issue comes into play for bound forms and controls using the
default Access error checking events.
Why don't you just let the user go edit->undo. In fact, even better is to
put the delete, or re-set option in the menu bar also. MUCH MUCH
more user friendly anyway...
Take a look at the screen shots here of some ms-access stuff, and note the
use of menu bars:
http://www.members.shaw.ca/AlbertKallal/Articles/UseAbility/UserFriendly.htm
My point here is that you CAN successfully use your custom verify routines,
and you can use it with bound controls.
Normally, there would be 2 ways to activate the command button. Either
via
the Accellerated letter (I.e. Alt-R) on the keyboard or via the mouse by
clicking on it. Problem with this: All events of the control are ran
through before the "Enter" Event of the command button is even ran, and if
any of the validation events in Access on the control that's being
validated
is triggered to cause the "Cancel" variable to be set to True, well guess
what, the Command button never even receives the focus.
Well, as mentioned, if a user is stuck in a text box due to bad data, you
must either:
Not let them out, or teach them to use the edit->undo. Or, if they want
to complete bail out, then again, give them a delete option in the menu bar.
And, note in my code I also have a global var for verification.
If bolNoVerify = True Then
Exit Function
End If
Every form I have has the above variable. if you don't touch, or forget
about
the bolNoVerify, then it has a value of false, and the verify code will
always run. However, if some situation comes up where you need to temporary
turn off the verify, then you simple go:
bolNoVerify = False
That's one such
case where you DON'T want error checking done on the control cause the
user
is resetting the form and disregarding any of the updating that would have
taken place.
As mentioned, in your example, you don't likely even need the bolNoVeify
trick, as you need to give users a undo option in the menu, or place that
re-set button in the menu bar...
So, all you have to do here is allow users to issue a un-do to the current
field, and this seems to have been over looked by you. I can see 1/2 dozen
more solutions here, and they all work VERY CLEANLY with the events that you
have at your disposal.
To address this issue within Access, I had to do the following:
Create a central modulated code to handle the bulk of the error checking
process
Excellent, the above is my approah also..it is works well.
Unbind every form and control
No, that is where you are going wrong here. You should not have to use
un-bound controls. I have FABULOUSLY complex error checking on some forms,
and I have NONE of your problems...
Any control that is to be validated when it is about to lose focus (which
will actually lose the focus, but cause of how I got it setup, the control
still gets validated). had to set one of the global error checking
variables
to the control via the Exit Event of the control that is to be validated.
Either you force the user to stay in that field, or you let them out. You
have to make a choice. You can usually train the user to used edit->undo to
get out of the offending error message (the undo command will ONLY un-do
changes to the current field). I also ALWAYS use a custom edit menu, and
also allow the user to delete the record REGARDLESS of the focus issue.
perhaps you should consider using custom menus here in place of a
button...as that is what most windows programs have anyway.
This way, when the Enter Event of the next control calls on the Validate
code
You are kidding me..right? This sounds very messy, as you have to attach
code to each control. (in my example, I can enter a list of fields that I
want as required..and I am done!!). What happens if the user hits shift-tab
to navigate backwards. Or, using the mouse to jump to another field. Or,
worse, what happens when the user jumps to buttion? Your
approach seems messy, too much work, and the fact that you are now going to
un-bound controls seems to prove this.
This took me 3 months to do the necessary research, development, and
debugging.
My above approach took me less then one evening..and I been using it for
years ever since. It solves virutally all of yoru prolblems, and takes
much less work, and much less coding and effort.
The above too
The reason why I spent such time on it, I'm real big on Error
checking at the appropriate times, but at the same time, if users can't
get
out of some control, thus LOCKED IN THE CONTROL cause of error checking
taking place at an INappropriate time, they will quit using the program in
a
heart beat. For this reason, I must be just as concerned about user
friendliness as I am about data validity.
I have some of the most incredable vierfying code in the world and my users
never get stuck, and it takes VERY little code to verify my forms...
