473,700 Members | 2,500 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Automatic Error Handling in Access

344 Recognized Expert Contributor
Thought I would give something back with a few articles. This article is a bit of code to add error handling. When I have time, I want to write articles on multilingual databases, and Access Security, but I'll start with something short and simple

This code was written in Access 2003 but should be valid in Access 2000

By default, when you start a new module, either in a form or report, or a global module, Access does not declare Option Explicit. This means a simple spelling mistake in your variable names could cause havoc with your code and you wont pick it up when you compile. All modules should have option explicit, and all variables should be explicitly declared. You can force Option Explicit on all new modules by opening a module and in the VBA window go to Tools / Options / Editor tab and tick Require Variable Declaration. This will put Option Explicit on all new modules.

When you start a new procedure, error handling is not added, you have to do it yourself. This is slow, tedious and repetative, exactly the sort of task a computer should do.

What I have written below are three procedures, designed to be run from the immediate window, that will add error handling to every bit of code, and add option explict to every module.

I now no longer write error handling, unless there is a special case. I just code all day, and run this routine at the end of the day to add all the error handling.

First important note. These procedures must be in their own module, I call mine basManualFuncti ons and then hard-code (as you will see below) "basManualFunct ions" as the one module to exclude. You cant edit a module that is open and running code.

Feel free to use these procedures (at your own risk), modify, copy, even sell them if you want. Acknowledgement to Lytton Consultants Ltd would be appreciated, but not needed. I acknowledge Litwin and Getz, from whose books I got most of my Access ideas.

Oh yes, backup before running this.

The first procedure is SetAllErrorChec king.

This is fairly basic, it loops through all the modules and calls processmod for each one.

It then loops through all the forms and reports and does the same for each module they hold.

Note that neither of these procedures have error checking themselves. That is deliberate, they are meant to be run hands on, and if an error occurs, I want to debug the code by hand.

Expand|Select|Wrap|Line Numbers
  1. Sub SetAllErrorChecking()
  2. 'This opens all code and sets error checking
  3. Dim cont As Container
  4. Dim mdl As Module
  5. Dim doc As Document
  6.  
  7.     Set cont = DBEngine(0)(0).Containers("Modules")
  8.  
  9.     For Each doc In cont.Documents
  10.         If doc.Name <> "basManualFunctions" Then
  11.             DoCmd.OpenModule doc.Name
  12.             ' Return reference to Module object.
  13.             Set mdl = Modules(doc.Name)
  14.             processmod mdl
  15.             DoCmd.Close acModule, doc.Name, acSaveYes
  16.         End If
  17.     Next doc
  18.  
  19. Dim i As Integer, j As Integer
  20. Dim db As Database
  21. Dim frm As Form, rpt As Report
  22.  
  23.     Set db = CurrentDb
  24.     For i = 0 To db.Containers.Count - 1
  25.         If db.Containers(i).Name = "Forms" Then
  26.             For j = 0 To db.Containers(i).Documents.Count - 1
  27.                 DoCmd.OpenForm db.Containers(i).Documents(j).Name, acDesign
  28.                 Set frm = Forms(db.Containers(i).Documents(j).Name)
  29.                 processmod frm.Module
  30.                 DoCmd.Close acForm, db.Containers(i).Documents(j).Name, acSaveYes
  31.                 ' DoCmd.Close acForm, db.Containers(i).Documents(j).Name, acSaveNo
  32.             Next
  33.         End If
  34.         If db.Containers(i).Name = "Reports" Then
  35.             For j = 0 To db.Containers(i).Documents.Count - 1
  36.                 DoCmd.OpenReport db.Containers(i).Documents(j).Name, acDesign
  37.                 Set rpt = Reports(db.Containers(i).Documents(j).Name)
  38.                 processmod rpt.Module
  39.                 DoCmd.Close acReport, db.Containers(i).Documents(j).Name, acSaveYes
  40.             Next
  41.         End If
  42.     Next
  43.  
  44.     Set db = Nothing
  45.  
  46.     Set mdl = Nothing
  47.     Set doc = Nothing
  48.     Set cont = Nothing
  49.  
  50. End Sub
  51.  
Now we look at processmod, which takes a module as a parameter.

First I print the module name, then assume option explicit has not been set. I scan all the lines up to CountOfDeclarat ionLines looking for option explicit. If I find it, set boolGot to true.

If i dont find it, I insert Option Explicit as the 2nd line in the module.

Now I start looking for each procedure. I can't use the module functions, because they allow a procedure to start, with comment lines, before the actual Private Sub or Public Function statement.

I scan for each of the possible procedure start lines and when found, call processProc, passing the procedure name, type, startline

This is the code for processmod
Expand|Select|Wrap|Line Numbers
  1. Sub processmod(mdl As Module)
  2. Dim intLine As Long, strLine As String, strProcName As String, intBrac As Integer
  3. Dim boolGot As Boolean
  4.  
  5.     Debug.Print mdl.Name
  6.     boolGot = False
  7.     For intLine = 1 To mdl.CountOfDeclarationLines
  8.         strLine = mdl.Lines(intLine, 1)
  9.         If Trim(strLine) = "Option Explicit" Then boolGot = True
  10.     Next
  11.  
  12.     If Not boolGot Then
  13.         mdl.InsertLines 2, "Option Explicit"
  14.         Debug.Print " Added Option Explicit"
  15.     End If
  16.     intLine = 0
  17.  
  18.     While intLine < mdl.CountOfLines - 1
  19.         intLine = intLine + 1
  20.         strLine = mdl.Lines(intLine, 1)
  21.         If Left(strLine, 3) = "Sub" Then
  22.             'We have a new Sub Routing
  23.             strProcName = Right(strLine, Len(strLine) - 4)
  24.             intBrac = InStr(strProcName, "(")
  25.             strProcName = Left(strProcName, intBrac - 1)
  26.             processProc strProcName, intLine, "Sub", mdl
  27.         End If
  28.         If Left(strLine, 10) = "Public Sub" Then
  29.             'We have a new Sub Routing
  30.             strProcName = Right(strLine, Len(strLine) - 11)
  31.             intBrac = InStr(strProcName, "(")
  32.             strProcName = Left(strProcName, intBrac - 1)
  33.             processProc strProcName, intLine, "Sub", mdl
  34.         End If
  35.         If Left(strLine, 11) = "Private Sub" Then
  36.             'We have a new Sub Routing
  37.             strProcName = Right(strLine, Len(strLine) - 12)
  38.             intBrac = InStr(strProcName, "(")
  39.             strProcName = Left(strProcName, intBrac - 1)
  40.             processProc strProcName, intLine, "Sub", mdl
  41.         End If
  42.         If Left(strLine, 8) = "Function" Then
  43.             'We have a new Function Routing
  44.             strProcName = Right(strLine, Len(strLine) - 9)
  45.             intBrac = InStr(strProcName, "(")
  46.             strProcName = Left(strProcName, intBrac - 1)
  47.             processProc strProcName, intLine, "Function", mdl
  48.         End If
  49.         If Left(strLine, 15) = "Public Function" Then
  50.             'We have a new Function Routing
  51.             strProcName = Right(strLine, Len(strLine) - 16)
  52.             intBrac = InStr(strProcName, "(")
  53.             strProcName = Left(strProcName, intBrac - 1)
  54.             processProc strProcName, intLine, "Function", mdl
  55.         End If
  56.         If Left(strLine, 16) = "Private Function" Then
  57.             'We have a new Function Routing
  58.             strProcName = Right(strLine, Len(strLine) - 17)
  59.             intBrac = InStr(strProcName, "(")
  60.             strProcName = Left(strProcName, intBrac - 1)
  61.             processProc strProcName, intLine, "Function", mdl
  62.         End If
  63.     Wend
  64.  
  65. End Sub
  66.  
Ok, so now we have the startline of a procedure, and have called processproc

ProcessProc assumes that you dont have error handling, and runs through the module until it finds End Sub or End Function.

It scans each line, looking for On Error. If it finds On Error anywhere in the procedure, it assumes you have error handling for this proc and ignores it. Thus, if you did not want error handling in a procedure, then you could add the comment line
' On Error no error handling here
and it would ignore that procedure.

If we find no error handling, we add the following

After the startline, we add
On Error Goto xxx_Err where xxx is the procedure name

At the end of the procedure we add the following lines. This can be tailored to your own error handling conditions

xxx_Exit:
Exit Sub (or Exit Function)
xxx_Err:
MsgBox Err.Description & "in xxx"
Resume xxx_Exit

Finally print out "added error handling" so I can see what has been added when all done.

So here is processproc
Expand|Select|Wrap|Line Numbers
  1. Sub processProc(ByVal strProcName As String, ByVal intStartLine As Long, ByVal strSubFunc As String, ByRef mdl As Module)
  2. Dim intThisLine As Integer, boolGot As Boolean, intLastLine As Integer, strText As String
  3.  
  4.     boolGot = False
  5.     intThisLine = intStartLine
  6.     While mdl.Lines(intThisLine, 1) <> "End " & strSubFunc
  7.         intThisLine = intThisLine + 1
  8.         If InStr(mdl.Lines(intThisLine, 1), "On Error") > 0 Then boolGot = True
  9.     Wend
  10.     intLastLine = intThisLine
  11.     If Not boolGot Then
  12.         Debug.Print " " & strProcName
  13.         strText = strProcName & "_Exit:" & vbCrLf
  14.         strText = strText & " Exit " & strSubFunc & vbCrLf
  15.         strText = strText & strProcName & "_Err:" & vbCrLf
  16.         strText = strText & " MsgBox Err.Description & " & Chr(34) & " in " & strProcName & Chr(34) & vbCrLf
  17.         strText = strText & " Resume " & strProcName & "_Exit"
  18.         mdl.InsertLines intLastLine, strText
  19.         mdl.InsertLines intStartLine + 1, "On Error Goto " & strProcName & "_Err"
  20.         Debug.Print " Added Error Handling"
  21.     End If
  22.  
  23. End Sub
  24.  
So copy the code, open the immediate window, type SetAllErrorChec king and enjoy

Mark Fisher
Lytton Consultants Ltd
Jun 21 '07 #1
0 11588

Sign in to post your reply or Sign up for a free account.

Similar topics

1
5032
by: Wayno | last post by:
My php logs are coming up empty. I have done all I can think of, and all that made sense to me. Can someone take a look at my php.ini please and tell me what you think may be the problem. I double-checked the path to my error log. It is in /var/www/logs/php_error_log Thanks. :) -Wayne Stevenson
1
8053
by: annie | last post by:
Hi all, I have recently ported my Access 2000 app to SQL Server, keeping the Access client as the front end using linked tables. I am also using triggers on my SQL tables to trap orphan records and validate added data. My question is..
3
3093
by: WindAndWaves | last post by:
I am writing error handling procedures at the moment. Here are some questions: 1. Can you write a procedure that picks up any error and deals with it no matter where it happens in the database? 2. Does Access keep a list of errors in a "secret" table? 3. Is there a way to handle common errors (such as - "you need to choose from the list" or "a related record is required in ....") with just one
16
2644
by: Steve Jorgensen | last post by:
I'm trying to figure out if there is a way to generate standard error handlers that "know" if the calling code has an error handler in effect (On Error Goto <label> or On Error Resume Next) before deciding how to respond. Does anyone know if this is possible.
6
8456
by: Squirrel | last post by:
I have a command button on a subform to delete a record. The only statement in the subroutine is: DoCmd.RunCommand acCmdDeleteRecord The subform's recordsource is "select * from tblVisit order by VisitDt" I'm getting this error message: Errno is 2465. Err.description is "Can't find field '|' referred to in your expression"
1
483
by: michaeltorus | last post by:
Hi I'm currently designing a new web application in .Net. I've pretty covered everything, apart from error handling. There seems to be a few different way to do this, but something I've read often is that loading all your code with lots of try / catch blocks is not the best way. My application is made up of a number of different sub applications. A CRM Package, with financial services, compliance and commisions etc etc. Each part has...
0
1464
by: cephal0n | last post by:
Hi All! I have two table tblHome1, contains all the unique PinNo and tblHome2 contains a duplicated PinNo and description. I put an automatic numbering (ProdID) and set it as my index. What I want to do is make an automatic filtering and labeling method for my table. The reason for this is because I m handling more than 500 records and labeling them would take me forever, so I started with what I can understand, which is seeking 5 records...
2
19476
hyperpau
by: hyperpau | last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding. I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com). Ergo, I will be writing this article intended for those who are in the same level, or maybe lower, of my technical knowledge. I would be using layman's words, or maybe, my own words as how I understand them, hoping, you will understand it the same way that...
0
2897
hyperpau
by: hyperpau | last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding. I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com). Ergo, I will be writing this article intended for those who are in the same level, or maybe lower, of my technical knowledge. I would be using layman's words, or maybe, my own words as how I understand them, hoping, you will understand it the same way that...
0
8726
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8645
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9214
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8925
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7810
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6561
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5903
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4404
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2392
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.