Results 1 to 7 of 7
  1. #1
    vector39 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    May 2017
    Posts
    76

    Error handling in VBA - General

    Hi everyone



    So this morning I was fiddling around with Access and trying to incorporate an inactivity timer that automatically logs users off the database (from http://www.accessmvp.com/JConrad/acc...e/kickoff.html). While it somewhat works, after closing then reopening the database I got the infamous Microsoft Access has stopped working crash and after a few more times of closing and reopening this database, Access automatically created a backup copy, for which I made an empty database and imported everything into it. Although I do plan on fiddling around with it more so that it is perfect, I realize, I have no error handling what so ever. Every vba code and module I have simply gotten from the internet and just modified it where necessary. I found this article http://allenbrowne.com/ser-23a.html which says "every function or sub should contain error handling" but looking at this code itself, is a bit daunting for the first time. I'm still a novice when it comes to vba. Do I have to include this script in every vba module now? I'm not sure what to do because it seems every time I incorporate some sort of vba, it causes some sort of crash. Any advice is much appreciated!

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    It is common practice to include Error Handling in EVERY Procedure, not just the module. Without them, anything is possible.

  3. #3
    vector39 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    May 2017
    Posts
    76
    Thank you. Do you have any examples that are reliable sir?

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    The first example Allen Brown gives in the link is the simplest as a starting point.

  5. #5
    Lena01 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Aug 2017
    Posts
    3
    IntroductionError handling refers to the programming practice of anticipating and coding for error conditions that may arise when your program runs. Errors in general come in three flavors: compiler errors such as undeclared variables that prevent your code from compiling; user data entry error such as a user entering a negative value where only a positive number is acceptable; and run time errors, that occur when VBA cannot correctly execute a program statement. We will concern ourselves here only with run time errors. Typical run time errors include attempting to access a non-existent worksheet or workbook, or attempting to divide by zero. The example code in this article will use the division by zero error (Error 11) when we want to deliberately raise an error.
    Your application should make as many checks as possible during initialization to ensure that run time errors do not occur later. In Excel, this includes ensuring that required workbooks and worksheets are present and that required names are defined. The more checking you do before the real work of your application begins, the more stable your application will be. It is far better to detect potential error situations when your application starts up before data is change than to wait until later to encounter an error situation.
    If you have no error handling code and a run time error occurs, VBA will display its standard run time error dialog box. While this may be acceptable, even desirable, in a development environment, it is not acceptable to the end user in a production environment. The goal of well designed error handling code is to anticipate potential errors, and correct them at run time or to terminate code execution in a controlled, graceful method. Your goal should be to prevent unhandled errors from arising.
    A note on terminology: Throughout this article, the term procedure should be taken to mean a Sub, Function, or Propertyprocedure, and the term exit statement should be taken to mean Exit Sub, Exit Function, or Exit Property. The term end statement should be taken to mean End Sub , End Function, End Property, or just End.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Allen Browne's function code would not go into every module. It would be in one general module then it could be called by any procedure. So a procedure would have its own 'On Error GoTo' line and the line branched to would call the generic error handler function and pass arguments to the function. The generic function and saving records to an error log table are optional, good practice but might not be needed in your situation. Could just have each procedure have its own customized error handler code as demonstrated in the FMS article linked in Allen Browne's.

    The function even has it's own error handler to deal with its own runtime failures.

    Be aware that an active error handler can actually make it harder to debug an issue. Sometimes need to comment the 'On Error GoTo' during a debugging effort. Then uncomment when resolved to reactivate.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Here is the full article that Lena01 posted in Post #5. It is by Chip Pearson (Pearson Software Consulting Services) at
    http://www.cpearson.com/excel/ErrorHandling.htm

    It describes the parts of error handling code.


    Also see
    Proper VBA error handling
    http://analystcave.com/vba-proper-vba-error-handling/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 09-06-2015, 12:06 PM
  2. Error handling of table update error?
    By panoss in forum Forms
    Replies: 5
    Last Post: 10-31-2014, 02:06 PM
  3. Replies: 3
    Last Post: 09-05-2012, 10:23 AM
  4. Replies: 0
    Last Post: 07-16-2012, 05:42 AM
  5. Error 2501 displays with Error handling
    By agent- in forum Programming
    Replies: 13
    Last Post: 08-05-2011, 02:20 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums