This is not a specific problem, but a question I have in general about VBA code. I have seen a lot of examples from beginners to experts that use error code numbers to process a procedure, not just to trap an error. In my opinion, I consider it a very poor approach to code design. I wanted to find out from people who have used Error handling to process their code, and why they did it, rather than find a way to handle the code that didn't involve invoking the Error protocols on Access VBA.
What type of examples do you have that justify Error handling over "proper" code design?
Using error handling is a must for any good programmer. A db opened in the runtime will just close if a runtime error is encountered and error handling was not used for the offending sub or function.
Trapping for a specific error is a must if you know the error could occur. That is why a good programmer will test and test and test their programs and trap for specific errors if they know that they could occur.
As an example, you have a VBA routine that prints a report and the user clicks the Cancel button while the report is spooling. If you do not trap for the runtime error # 2501 the user would get a runtime error message because of their action. If you were trapping for the runtime error # 2501 then you could provide a custom error message or just have the error handler exit that function for the runtime error # 2501.
But hey, that is just my opinion. Maybe your users are perfect and they will never do anything that could produce an unexpected or expected runtime error.
Code:
Private Sub bDeleteRecord_Click()
On Error GoTo bDeleteRecord_Click_Err
If Me.Dirty Then
Beep
MsgBox "Please Save This Record!" & vbCrLf & vbLf & "You can not delete this record until you either 'Save' the changes made to this record or 'Undo' your changes.", vbExclamation, "Save Required"
Else
Beep
If MsgBox("Delete record for vehicle " & LicenseNumber & "?", vbQuestion + vbYesNo, "Delete Current Record?") = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
End If
End If
bDeleteRecord_Click_Exit:
Exit Sub
bDeleteRecord_Click_Err:
If Err = 2046 Then 'The command or action 'DeleteRecord' isn't available now
Exit Sub
ElseIf Err = 2501 Then 'The RunCommand action was canceled
Exit Sub
Else
MsgBox Err.Number & " - " & Err.Description
Resume bDeleteRecord_Click_Exit
End If
End Sub
Of course I am emphatic about error handling. But I'm not talking about the coding that is done to prevent runtime or procedural errors from unconventional operations, such as canceling, etc. Let me try to give an example.
Let's say you are trying to update an open recordset, and you try to update a record with a duplicate key. Sometimes coders don't bother preventing the duplicate entry, instead letting the On Error GoTo procedure to send the code to the Error handler, where the particular Error handler would identify the Error code for duplicate entry, and then process the duplicate record there. My contention is that there should be a more active method of identifying the duplicate record, either through a DLookup function or, preferably, through a query. Since it's a normal situation to prevent duplicate records from being entered into a table, I contend it should be done through robust coding than the "backdoor" error handling procedure.
Maybe I'm splitting hairs, but just need to hear other's thoughts on this.
I agree that some apps don't actively anticipate common errors and provide meaningful feedback to the user when the error hits.
But you can actively use the Err Object. And assuming the error is the exception rather than the norm, letting an Error handler catch a boo-boo is usually more efficient because you will save on the processing time you incur checking for an error before every "try" -- where try represents whatever you're attempting to do. Another perspective: writing error checking code is often redundant -- the Err object (like any object) is code already written and debugged by somebody else to do the work for you; tapping into it generally saves you time and standardizes your work, which will make it easier for you to understand your own code in the future when -- if you're like me -- you've forgotten most everything else about it...
Code:
Function AddRecord(NewRecID As Long, _
Optional sFirstName As String = "") _
As Boolean
On Error GoTo HandleError
Dim sSQL As String
sSQL = "INSERT INTO MyTable (RecID, FirstName) " & _
"VALUES (" & NewRecID & "," & "'" & sFirstName & "'" & ")"
CurrentProject.Connection.Execute sSQL
AddRecord = True
Exit Function
HandleError:
Select Case Err.Number
Case -2147467259 'Duplicate
MsgBox "No Dups Please" & vbCrLf & vbCrLf & Err.Description, vbCritical, "Duplicate"
Case -9999999999999999 'Something else
MsgBox "Etc"
Case -5555555555555555 'Another Something else
MsgBox "Etc2"
Case Else
MsgBox Err.Description, vbCritical, Err.Number
End Select
End Function
G’day mresann and please ‘split hairs’ all you like, it’s an excellent thread and thanks for starting it.
Let’s just hope that many others share their experiences and preferences.
If I may, a little bit of deliberate error generating with which to start: -
Code:
Option Explicit
Option Compare Text
Public Sub ParkCursorInMeAndHitF5_WhatDoWeGet()
On Error GoTo ErrorHandler
TestIt
MsgBox "Error " & Err.Number & ": " & Err.Description
ExitProcedure:
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProcedure
End Sub
Public Sub TestIt()
Dim lngX As Long
On Error GoTo ErrorHandler
lngX = 1 / 0
ExitProcedure:
Exit Sub
ErrorHandler:
lngX = 1 / 0
Resume ExitProcedure
End Sub
Well what we get is a subtle transfer of an error back to the caller that would possibly not expect it.
And all it took was a very simple one-liner: - lngX = 1 / 0
(One simple error for man…one giant error for mankind.)
(I got that from somewhere but don’t think it was on this planet. )
So if we take the point of view that “the more code we write…the more errors may occur” then I think we come to a ‘partial conclusion’ that code written within an error handler either needs to be bullet proof (which is almost impossible) or kept to a minimum.
However, and there is almost always a however, simple error handling code is usually restricted to reporting the error but not fixing the problem.
There are other considerations to be made as well, not the least of which is to protect data in the instance of an error, and hardware solutions that are not generally available to PC users. (Old hat in minicomputers but fencing registers, error detection and correction of RAM, and watchdog timers (the ultimate shutdown) spring to mind.)
Now it might seem somewhat esoteric to mention these things but they had something in common.
Their aim was to protect the data, or process, and not to allow a wayward computer corrupting the very thing it was built to ‘order’.
Strangely, the example you gave has another and opposite approach which involves deliberately raising an error to overcome that particular problem in a multi-user environment.
As you might detect, this one of my pet subjects but I won’t bore you anymore... at the moment.
My contention is that there should be a more active method of identifying the duplicate record, either through a DLookup function or, preferably, through a query. Since it's a normal situation to prevent duplicate records from being entered into a table, I contend it should be done through robust coding than the "backdoor" error handling procedure.
- There is a more active method! It is called Declarative Referential Integrity and it is enforced by your database engine. Add my name to the list on the side of "let the db engine do it". I never write code to duplicate an existing function provided by my RDBMS. It is wasteful of resources and increases the risk of programming errors since the more code you write, the more code you have that could contain errors. I do frequently add my own messages in place of those generated by Access to give my users a friendlier alternative.
The example you posted is actually a prime example of why it is correct to let the db engine do it rather than doing it yourself. In a busy multi-user environment it is quite possible for someone to add a "duplicate" record between the time you check for an existing record and the time you add a new record. Now, I admit, this is not a frequent scenario but why take the chance?
OK, thanks for the insights. I actually used the duplicate record example as probably the most common use of the Error Handling function to process code which, on the surface, I find "lazy". Indeed, if you've seen my code in some of my examples, I use a standard msgbox notification system, but I also have another function error handler template which allows errors to be recorded to a log or db in a class or global module. Although I would use the class module in a solution release, I use the global module for most development and testing purposes.
However, when I code for trapping errors, they are for handling exceptions, rather than for a 'normal' processing decision, such as trying to pass a duplicate record into a primary key. In addition, sometimes I pass errors back in the calling queue if it makes more sense to process the code in that fashion. In any case, I record every error through a text file or, if the customer requests it, in a database entity, even if the error is processed behind the scenes from the user.
In retrospect, I think I am splitting a few more hairs than I should have in this case. I think it's more of a complaint of bad coding practices than error handling, but then again it's a personal preference.
“In retrospect, I think I am splitting a few more hairs than I should have in this case.”
Like hell you are…I can split hairs as well and this is the exact topic in which to do it.
The problem is that, as I see it, we are talking about errors; but in this thread the concept of an error has not yet been defined.
So I will ask a question or three…
1. If we write code that is designed to deliberately cause an error (exception), is it an error if the error (exception) is raised or is it an error if the error (exception) is not raised?
2. Do we attempt to handle the error (exception) or the failure to raise it?
3. What, therefore, is the difference between an error and an exception?
I thought this was a good thread from the outset, but if we don’t at least try to define that of which we speak then where will we go…down that dark cul-de-sac from which there is no return?
We need a limit of competency placed on the software, and/or computer, beyond which the software, and/or computer, is deemed incompetent.
An error handler may very well be deemed incompetent.
