Solved Error Trapping (1 Viewer)

Kayleigh

Member
Local time
Today, 14:35
Joined
Sep 24, 2020
Messages
706
Hi,
I am working on tidying up a large database so it is suitable for consumer use. Part of the process is embedding error trapping into all code. I can do with some advice on this please.

I found this post quite useful. But a few questions I have:
1. Any advice on cleanup code (e.g. close rs/db) for exit?
2. What common errors to accommodate?
3. Is it really necessary to log every error that occurs?
4. When would 'on error resume next' be suitable?

This is my generic code for reference:
Code:
On Error GoTo Err_SubName

//Sub Code

Exit_SubName:
    Exit Function

Err_SubName:
    MsgBox Err.Description, vbExclamation, "SubName Error " & Err.Number
    Resume Exit_SubName
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:35
Joined
Feb 28, 2001
Messages
26,999
You have a couple of options. I'll answer the questions with my OPINION ('cause I've been known to be wrong.) You have a choice as to whether you can continue your code after cleanup or whether the error code is the end of what you are doing. You can do both sometimes.

1. Any advice on cleanup code (e.g. close rs/db) for exit?

IF you had opened something in the sub/function AND you are NOT going to allow the code to finish normally, then you absolutely SHOULD close what you opened, if only to assure it is left in the proper state. If it is a recordset, there is some question as to whether the explicit CLOSE is needed, but if it is an application object then a CLOSE should be mandatory. In your code sample, you are showing a "Resume Exit_Subname" that flows into the sub exit code. This implyies that you would NOT continue whatever the subroutine was doing. So this is an "ABORT" logic flow. It is a valid choice if the ABORT case makes sense for your situation.

2. What common errors to accommodate?

If you debugged your code well enough, you will nave no errors to accommodate. Unless you expect users to input cruddy answers to questions or prompts or text boxes. I.e. Watch out for garbage from users. NO, I'm not saying your users are garbage. But they ARE human and make garbage-input types of mistakes. So ALWAYS expect the unexpected from user actions. Other expectations will depend on the vagaries of your specific process.

3. Is it really necessary to log every error that occurs?

No. For instance, if you expect certain math errors in a routine and have a "fix" for such errors, then maybe you don't want to remember that you had the error (because you "fixed" the logic flow). If you expect to do file work and you get a FILE NOT FOUND, that might be worth logging if the file simply HAS to be there - but you would not log it if it is NOT required. Every case is different. In one of my older DBs, I had logging turned on because I was capturing inputs that led to errors that I needed to actually SEE in order to fix. Those errors, I logged but when I fixed the problem, I left the error handling code intact just in case another error was involved later.

4. When would 'on error resume next' be suitable?

Sometimes I do this when the error is truly immaterial - or expected and correctable. For instance, if you get a "FILE NOT OPEN" when you were trying to close a file, maybe you don't care. You were going to close the file anyway and you find that it wasn't open. After the error, the file is not open, which is what you wanted. So you could do a RESUME NEXT.

It is actually quite common for complex cases to have multiple responses / paths to an error trap. In such cases, you RESUME NEXT for those errors that don't matter or that can be cleaned easily without harm, but sometimes you do cleanup and an "alternate" End Sub/End Function where you want some clarification. One size doesn't fit all.
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:35
Joined
Jan 23, 2006
Messages
15,363
Can you expand on the type of things involved (besides Error Logic)?

working on tidying up a large database
 

Kayleigh

Member
Local time
Today, 14:35
Joined
Sep 24, 2020
Messages
706
So the majority of the subs are related to opening various forms on filters, dynamically assigning values to textboxes based on other queries, writing DAO recordsets, calculating values on forms on-the-fly, using Outlook features, running events based on value selected from combo box, form validation etc.

Would appreciate if anyone could expand on suitable error trapping for the above functions - more than the generic code I illustrated above.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:35
Joined
Feb 28, 2001
Messages
26,999
When dynamically creating filter strings, queries, or anything else that can be dynamically built, you will probably get a lot of syntax errors (because whatever dynamic thing you built, you built it wrong). It is a cost of doing business so don't feel terrible for having such things. To quote that great philosopher Forrest Gump, "It happens."

Such syntax errors tend to be show-stoppers. I.e. if you get this error, you have to stop and regroup. Sometimes you need to include a status code as a return from a subroutine even if it is not a function. Which usually means EITHER returning a ByRef status code or declaring some public variable in a general module as a Go/NoGo flag, or using TempVars elements for status returns.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:35
Joined
Feb 19, 2002
Messages
42,970
Create a standard error trap and copy it everywhere. Use a case statement with an else to trap unanticipated errors.
Code:
On Error GoTo Err_SubName

//Sub Code

Exit_SubName:
'''clean up code goes here
    Exit Function

Err_SubName:
    Select Case Err.Number
        Case ...
            MsgBox Err.Description, vbExclamation, "SubName Error " & Err.Number
        Case 2501
            Resume Next
        Case else
            MsgBox Err.Description, vbExclamation, "SubName Error " & Err.Number
    End Select
    Resume Exit_SubName

Most of the time I just wait and fill in the case statement as I find errors. When you open a form or report and there is a a chance it won't open due to no data, you can usually trap that with 2501 but occasionally, there are other errors instead so I test to see what will happen.
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:35
Joined
Jul 9, 2003
Messages
16,244

Isaac

Lifelong Learner
Local time
Today, 07:35
Joined
Mar 14, 2017
Messages
8,738
1. Any advice on cleanup code (e.g. close rs/db) for exit?
Unlike almost everything else in VBA, it IS an especially good and useful idea to set DAO recordsets to nothing when done. If many of them are used (such as a Function referenced by a Query which means it's run hundreds of times), and not explicitly set to nothing, I've heard and experienced that bad results can occur.

Most variables and objects don't need any cleanup, VBA sort of collects garbage when things go out of scope.

Personally, here is my most common "cleanup" in error handlers: Closing/quitting Excel application instances, or Excel workbooks, in case of an error. However, the error handler itself can cause another error if I code like objExcelApp.Quit , at such a time when Excel isn't open yet. Therefore, I do this a LOT:
Code:
Sub Foo()
dim excelapp as object, blExcelIsOpen as boolean
On Error Goto Errhandler

'..........various code does things here'

'Now time to open Excel:
set excelapp=CreateObject("Excel.Application")
blExcelIsOpen = True

'.........various code does things here

'Now time to close Excel:
excelapp.quit
blExcelIsOpen=False

'.........various more code

Exit Sub
errhandler:
if blExcelIsOpen=True then
    excelapp.quit
end if
End Sub
4. When would 'on error resume next' be suitable?
Almost never. Close to 100% of the "on error resume next" you see commonly in beginners' code is completely inappropriate.
However, I do use it occasionally. To deliberately allow an Err to be raised, if possible, and then test to see if an Err has been raised:
Code:
Function TableExists(strName as string) as boolean
dim td as dao.tabledef
on error resume next
set td=currentdb.tabledefs(strName)
if err.number<>0 then
    TableExists=False
else
    TableExists=True
end if
End Function
And in VBScript, of course, On Error Resume Next (and then testing for the ERR) is practically required.

3. Is it really necessary to log every error that occurs?
I have a feeling you might not have actually meant "log", but rather "trap". I.e., "include error handling". Did you?
It certainly isn't necessary to log every error that occurs, although it's a nice bonus.
The biggest problem I have seen with super-fancy extensive error logging is probably the fact that it is difficult to guarantee separation between the success of the error handling function itself, versus the database that isn't working (else an error wouldn't have occurred). I.E., if someone has a problem with a connection....or back end link.....or email/outlook.....or references...etc., then your fancy table-driven error logging might not work either, leading to double complicated reporting.

Probably the best I have seen in this regard is an app that added Error information as windows Events - and then had a one-click function to "send event log [filtered] by email".
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:35
Joined
Sep 12, 2006
Messages
15,613
You really have to think about what you are trying to do.

Take something "simple" like trying to produce a disk file.

You may not be able to produce the file because

a) the file already exists
b) the file exists and is in use
c) the file doesn't exist, but the new file cannot be produced for some reason. Maybe no write permission on the folder.
d) the specification may be illegal
e) the hard disk may even be full

each of these possibilities will give you an error, and you need a way to manage the error. Can you ignore it, or do you have to manage your program in a different way depending on the error.

Basically ANY action that involves I/O actions needs error handling. Any arithmetic process that could overflow needs handling.

on error resume next is only really acceptable when as the programmer you know that the error is expected and can be ignored.
eg - in the above example you decide to delete the old file before creating a new one. If there is no file to delete you will get an error, but you know you can ignore that. However if the delete fails because the file is in use, then you know you can't ignore the error and carry on as if the error didn't happen.
 
Last edited:

Users who are viewing this thread

Top Bottom