Error Handling Where? When?

[PeterM]

I'm now at the stage that I need to build in some error handling in my
database. The question I have is say for example, I have a field that is
required on a form. I have a Save button attached to the form.

Should I check for the required field when the required field looses focus?
or in the Before Update procedure? or in the Form_Save event?

I tend to write a procedure that will scan the entire form and display a
generic form listing all errors and warnings at once instead of doing one at
a time. It that unnecessarily complicating the process?

Thanks for your advice!

 

[.Len B]

Hi Peter,
Sounds like you're talking Data Validation rather than Error Handling.
I tend to think of Error Handling as dealing with program or logic errors
rather than a user entering bad data.

My practice is to advise the user as quickly as possible of bad data
entry so
I prefer to use the control's AfterUpdate and then SetFocus to the bad
data.
However, if the user clicks past a required field then that event never
fires so
you must catch it before saving.

For data that depends on or is inter-related with other data, and to
catch a click-past
I use the Form_BeforeUpdate event. You could also use it as a final check
before saving.
Here is an outline for that which Allen Browne gave me some time ago.

'Handle required stuff first
If IsNull(txtContactDate) Then
Cancel=True
strMsg = strMsg & "A valid contact date is required."
End If
....

'Now handle warnings
If Not Cancel Then
If Len(txtContactName) < 5 Then
blnWarn=True
strMsg = strMsg & "Contact Name is very short"
End If
....
End If

'Finish message and display MsgBox
If Cancel Then
strMsg = strMsg & vbCrLf & "Correct the entry, or press <Esc> to
undo."
MsgBox strMsg
ElseIf blnWarn Then
strMsg=strMsg & vbCrLf & vbCrLf & "REALLY?"
If MsgBox(strMsg, vbTesNo + vbDefaultButton2, "Are you
suuure?")<> vbYes Then
Cancel = True
End If
End If

HTH
--
Len
______________________________________________________
remove nothing for valid email address.
| I'm now at the stage that I need to build in some error handling in my
| database. The question I have is say for example, I have a field that
is
| required on a form. I have a Save button attached to the form.
|
| Should I check for the required field when the required field looses
focus?
| or in the Before Update procedure? or in the Form_Save event?
|
| I tend to write a procedure that will scan the entire form and display
a
| generic form listing all errors and warnings at once instead of doing
one at
| a time. It that unnecessarily complicating the process?
|
| Thanks for your advice!

 

[John W. Vinson]

I'm now at the stage that I need to build in some error handling in my
database. The question I have is say for example, I have a field that is
required on a form. I have a Save button attached to the form.

Should I check for the required field when the required field looses focus?
or in the Before Update procedure? or in the Form_Save event?

The BeforeUpdate event fires right before the record is written to disk (that
is when you need it), and it can be cancelled (unlike the LostFocus event) so
it's the best place for this.

I tend to write a procedure that will scan the entire form and display a
generic form listing all errors and warnings at once instead of doing one at
a time. It that unnecessarily complicating the process?

It's up to you: Access won't care but your users may thank you (for getting
nagged once with three warnings rather than three times in succession). It's
certainly more work but I'd be inclined to do it if you're up to it.

 

[PeterM]

Oh! I'm up to it. As a matter of fact I just finalized the error processing
for my system. I now edit the form for data errors. As errors are found I
call a subroutine to insert a row into an errorswarnings table with the error
information (controlname, formname, errordesc, etc). In the beforeupdate
procedure I query the errorswarnings table and if found, I open a generic
form displaying the error information. I've also added functionality to
allow the users to leave the error form open (popup=yes, modal = no) so they
can change the form based on the errors listed. After making corrections and
press the save button, I call the routine again and update the error form.
If everything is OK the errors/warnings form is closed and the record is
saved.

I've showed one of my end users and they are thrilled that all
errors/warnings are displayed at once and they really liked the ability to
change the data entry form while the errors/warnings are displayed and then
resubmit the save once they correct all errors.

Thanks to everyone for their ideas!

Pete

 

[John W. Vinson]

Oh! I'm up to it. As a matter of fact I just finalized the error processing
for my system. I now edit the form for data errors. As errors are found I
call a subroutine to insert a row into an errorswarnings table with the error
information (controlname, formname, errordesc, etc). In the beforeupdate
procedure I query the errorswarnings table and if found, I open a generic
form displaying the error information. I've also added functionality to
allow the users to leave the error form open (popup=yes, modal = no) so they
can change the form based on the errors listed. After making corrections and
press the save button, I call the routine again and update the error form.
If everything is OK the errors/warnings form is closed and the record is
saved.

I've showed one of my end users and they are thrilled that all
errors/warnings are displayed at once and they really liked the ability to
change the data entry form while the errors/warnings are displayed and then
resubmit the save once they correct all errors.

Sounds impressive, Pete! If it's sufficiantly general, would you consider
posting it to one of the Access sites so others can use it?

 

[(PeteCresswell)]

Per PeterM:

I've showed one of my end users and they are thrilled that all
errors/warnings are displayed at once and they really liked the ability to
change the data entry form while the errors/warnings are displayed and then
resubmit the save once they correct all errors.

One joker in the deck - that does not seem to have been played in
your game...and hopefully will not be - is cross-validation.

i.e. One wants to check the contents of some field against
values/computations on other tables/columns besides what is on
the screen.

No problem doing it... but it demands a different approach.

 

[Tony Toews [MVP]]

Oh! I'm up to it. As a matter of fact I just finalized the error processing
for my system. I now edit the form for data errors. As errors are found I
call a subroutine to insert a row into an errorswarnings table with the error
information (controlname, formname, errordesc, etc). In the beforeupdate
procedure I query the errorswarnings table and if found, I open a generic
form displaying the error information. I've also added functionality to
allow the users to leave the error form open (popup=yes, modal = no) so they
can change the form based on the errors listed. After making corrections and
press the save button, I call the routine again and update the error form.
If everything is OK the errors/warnings form is closed and the record is
saved.

Or you could use a listbox on the bottom of the main form. You could
manually add items using listbox.additem thus saving the need for a
separate form and table.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/