Generic Error Handling routine

[Albert D. Kallal]

How can I provide a generic error handling routine on every form
to handle common errors, like PKey, required, Duplicate rec, has child
records etc,

invalid mask editing, etc, without having to use the Validation Rules
(I think validation rules is the most confusing part of Access...and I have
used many programming tools for many years)

Hum, I never used validation rules to solve this problem. I not really sure
how you could do such a thing.

In most of my forms, I provide my own delete button. I also as a general
rule hide the ms-access interface complete.

So, for example, the code behind my delete button on form to delete a hotel
is:


CurrentDb.Execute ("delete * from tblHotels where id = " & myForm.ID)

The above is one line of code, and no error messages of any type about child
records etc will occur.

However, often you DO WANT to warn the user that deleting the record might
be a bad idea. In the above example, the hotel is for a tour reservation
system I wrote. I REALLY DO NOT want users to delete hotels if they are in
fact being used. So, I need to check if any child records exist BEFORE I
attempt the delete. (you will have a rather rotten user interface if you
start the delete process..and trap a lame error message about child
records). So, in fact, I use the following:


strError = ""

' lets look any tours that use this hotel

MySql = "select * from tblTours where Hotel = " & myForm.ID
Set rstBookings = CurrentDb.OpenRecordset(MySql)

If rstBookings.RecordCount > 0 Then
strError = "This Hotel is being used by some tours, and cannot be
deleted"
GoTo Berr
End If

' ask to delete
If MsgBox("Delete this Hotel ?", vbCritical + vbOKCancel +
vbDefaultButton2) = vbOK Then
CurrentDb.Execute ("delete * from tblHotels where id = " & myForm.ID)
DoCmd.Close acForm, myForm.Name
End If


Berr:

If strError <> "" Then
MsgBox strError, vbCritical
End If

rstBookings.Close
Set rstBookings = Nothing

As you can see, I don't even allow the user to delete a hotel that is in use
If you just trap the error,a nd display a message, the user can still delete
the hotel..and make real mess of existing tours (in fact, the cascade
deletes would delete all tours and users and invoices and huge chine of
tables by just deleting the ONE hotel.

My point here is that you want to provide user friendly messages anyway.
Further, often you need to warn, or in the above case NOT ALLOW users to
delete the record they are viewing.

Here is another example snip that I use when you try to delete a customer in
the reservation system.:

lngMainId = MyAForm!ID

' check if it is legal to delete this record. (no past tours can be on
file)...

Set rstPastTours = CurrentDb.OpenRecordset("select main_id from tblBgroup
where main_id = " & lngMainId)

If rstPastTours.RecordCount > 0 Then
bolDelete = False

MsgBox "This customer cannot be deleted due to past trips, or" &
vbCrLf & _
"having active invoice information. You must remove all history
information" & vbCrLf & _
"such as past trips and invoices before you can delete.",
vbInformation + vbOKOnly, "Sorry, name cannot be deleted"

Else
Beep
If MsgBox("Do you want to Remove this Name?", vbYesNo + vbCritical +
vbDefaultButton2, "Delete Rides Customer") = vbYes Then
bolDelete = True
End If

End If

' wrap up
rstPastTours.Close
Set rstPastTours = Nothing

If bolDelete = True Then
CurrentDb.Execute "delete * from tblmainclient where id = " &
lngMainId
DoCmd.Close
End If


Again, trapping the error message in some global routine would NOT make the
application user friendly. In just about every case, you need to explain to
the end user WHY the record cannot be deleted and not give some lame err
message about child records about to be deleted. So, I don't know how, even
why one would use validation rules here. You simply need to type in some
nice messages, and give the user some nice information as to why they cannot
delete the record. So, the above is far cleaner then trying to cobble
together some validation rules (I not at all clear why, or how you could use
validation rules to do the above anyway??).

While I do have referential integrity and cascade deletes enable..you can
see that it is really only ONE LINE of code to do the actual delete of the
above customer. However, the rest of the code is for friendly user
stuff..and that is where the validation code thus goes.

If you do distribute a mde, then any un-trapped errors will NOT re-set your
variables...and thus for this reason I always give my users a mde file...and
so should you. Thus, using a mde does kind of give you a global catch all
where at least your vars don't get re-set.


--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn

 

[TC]

You could try writing a common procedure that you can call from every form's
Form_Error event.

But sadly, Form_Error does not expose enough information to make this
valuable in every case. For example, Form_Error does not expose the exact
text of the actual error message; so you can not, for example, extract a
relevant table name from that message, & so on. Grrrrrrrrrrrrrrrrrrrrr! :-(

HTH,
TC

 

[Savvoulidis Iordanis]

How can I provide a generic error handling routine on every form
to handle common errors, like PKey, required, Duplicate rec, has child
records etc,
invalid mask editing, etc, without having to use the Validation Rules
(I think validation rules is the most confusing part of Access...and I have
used many programming tools for many years)

I would also like to get the column's label that is involved in the error,
so that I can provide it in my own message.
Is that possible in the form's error event (or in any other place ?)

TIA
Savvoulidis Iordanis
Greece

 

[Malcolm Cook]

Coding off the top of my head....

'create a class, say clsMyGenericFormHandler
'that has a

public frm withevents as access.form

'and then define

private sub frm_Error ....

'now, back in your form, make sure you ahve a form level variable defined as

private mgfh as clsMyGenericFormHandler

'and back in your form's open routine you have to include:

set mgfh = new clsMyGenericFormHandler
mgfh.frm = Me


'and don't forget to free it up in the _close routine:

set mgfh = nothing

'now, all your error's should be sinked to the custom error proc


You can sink other events this way too. For instance, this is a good place
to globally override default close behaviours if needed.


Regards,

 

[test]

Nice idea! Not sure that it is any simpler/shorter, though.

Cheers,
TC