Home > Articles > Data > Access

  • Print
  • + Share This

Responding to Runtime Errors

Whether you're creating an Access application to use yourself, for a few colleagues to use, or for a much wider audience, your objective should be to do all in your power to make sure errors can't occur when your application runs. Unfortunately, in all but the simplest applications, that ideal is not possible because you're not completely in control of what a user does.

Three categories of errors are possible in an application:

  • Syntax errors  These are errors in the use of the Visual Basic language. The Visual Basic Editor detects these types of errors automatically so that you can correct them.

  • Logic errors  Even though your VBA code has no syntax errors and it runs without indicating the presence of errors, it might not produce the results that you intend due to the presence of logic errors. You can find errors of this type only by extensively testing your application using a wide range of conditions and circumstances.

  • Runtime errors  These are errors that occur due to some unanticipated problems while your program is running.

This section shows you how to deal with runtime errors.

A few of the things that can happen while your application is being used are as follows:

  • Enough disk space might not be available for an operation.

  • A file that's needed might not be present or might not be accessible.

  • An attempt might be made to divide by 0 (zero).

  • A printer might not be available, might not be turned on, might have jammed, or might run out of paper.

  • An attempt to make a dial-up connection might fail because the phone line is busy.

These are just some specific examples that are quite easy to think of. For each of these examples, you could probably write code that checks for that specific error condition before allowing code that might encounter that error to run. For example, you could write code that compares the disk space required by some data with the available disk space before the code that attempts to write data to disk runs. That might be okay if you're concerned only with a disk on the user's local computer to which only that user has access. What happens, though, if data can be written to a shared disk? In that case, it's possible that in the interval between testing the disk space and attempting to write to the disk, someone else writes a file to the same disk.

The point is that, however hard you might try to anticipate possible errors, you're unlikely to be completely successful. For that reason, VBA makes it possible for you to detect almost any error that occurs while an application is running and to write code that allows a user to deal with that error. The errors that you can detect and deal with in this way are known as trappable errors. The methods of dealing with these errors are known as error trapping. By incorporating error trapping in your VBA code, you ensure that your application isn't likely to crash when the unexpected occurs

NOTE

If you don't provide for trapping errors within your code, VBA generates its own error messages. These error messages mostly don't convey meaningful information to a user. They don't provide any way for a user to recover from the error.

The Help topic "Trappable Errors" contains a complete list of trappable errors. Each trappable error has an error number and an error message.

The general method for trapping errors has four parts:

  • Setting the trap

  • Creating an error handler

  • Resuming program execution

  • Clearing the trap

These four parts are described in the next sections.

Setting the Error Trap

Each procedure should normally have a statement that sets an error trap before the first executable statement. Most people place this statement immediately after any declaration statements at the beginning of a procedure. The syntax of an error trapping statement is this:

On Error GoTo linelabel

Here, linelabel marks the place in the procedure where error-handling statements begin. A line label is text followed by a colon. No spaces or punctuation characters are allowed in a line label.

Listing 2 is a skeleton of a procedure with error trapping included.

Listing 2  Typical VBA Code to Set an Error Trap

Sub SubName()
   On Error GoTo ErrorHandler
   normal procedure statements
   Exit Sub
ErrorHandler:
   error handler statements
End Sub

When this procedure executes, the first statement sets up error trapping for all the subsequent statements in the procedure. Two possibilities exist:

  • All the subsequent statements execute without detecting a trappable error. In this case, the statements proceed as far as the Exit Sub statement, at which time the procedure terminates and error trapping is turned off.

  • A trappable error occurs when one of the statements in the procedure executes. At the first trappable error, execution switches to the first statement after the ErrorHandler line label and proceeds from there.

Now, let's turn to a practical example. Previously in this chapter, the section "Executing DoCmd Methods" showed this example of an event procedure:

Private Sub cmdSpreadsheet_Click()
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
   "tblPublisher", "C:\My Documents\Publisher.xls"
End Sub

As explained in that section, this event procedure attempts to copy the content of an Access table to an Excel workbook. The statement creates the file C:\My Documents\Publisher.xls if that file doesn't already exist. If the file does already exist, the statement alerts you to that fact and asks whether you want to replace the existing file.

It's quite possible that this event could cause an error for several reasons, one of the most obvious of which is that there isn't enough space on the disk for the new Excel workbook. To control what happens when an error occurs, you need to create an error handler—VBA code that executes when an error occurs.

After you've set an error trap, that trap remains in effect until one of the following statements executes:

Err.Clear

Exit Function

Exit Property

Exit Sub

On Error GoTo 0

On Error GoTo line

On Error Resume Next

Resume

Resume line

Resume Next

Instead of trying to remember that list, think of the lifetime of an error trap in this way. An error trap remains in effect until one of the following happens:

  • You explicitly clear it with an Err.Clear or On Error GoTo 0 statement.

  • The end of the procedure in which the trap is set is reached.

  • An error is trapped and then code execution resumes.

As mentioned in the section "Using Custom Procedures," previously in this chapter, you can call a procedure from within another procedure. If you set an error trap in one procedure and, while that error trap is in effect, call another procedure, the error trap remains in effect while the called procedure runs.

Creating an Error Handler

You can incorporate error trapping into the procedure described in the previous section by modifying it as shown in Listing 3.

Listing 3  Error Handler with Message to the User

Private Sub cmdSpreadsheet_Click()
   On Error GoTo ErrorHandler
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
   "tblPublisher", "C:\My Documents\Publisher.xls"
   Exit Sub
ErrorHandler:
   MsgBox "Can't copy the data to a spreadsheet."
   Resume Next
End Sub

With this improvement to the code, the data is successfully copied to the workbook if no error occurs. The code executes as far as the Exit Sub statement, at which point the procedure terminates. If you omit the Exit Sub statement, the subsequent error-handler code executes, even if no error occurred.

If an error, such as insufficient space on the disk, does occur, the error-handler section of the code displays a message saying that the data can't be copied. The Resume Next statement causes the execution of the next statement after the one that caused the error. The overall result is that the data isn't copied to the workbook, but the user can continue with other operations.

The error-handler section of the code can do much more than is shown in this simple example. For example, the error-handler section could make it possible for a user to save the workbook on another disk. But that assumes that the reason for the user being unable to save the workbook is that insufficient disk space is available. Perhaps there are other reasons for being unable to save the worksheet.

Analyzing Errors

When an error occurs, VBA creates an error object, Err, that has several properties, the most useful of which are listed here:

  • Number—Error number

  • Description—Text that describes the error

  • Source—Name of the project in which the error occurred

You can use these properties in an error handler as shown in Listing 4.

Listing 4  Error Handler with Number, Description, and Source Properties

Private Sub cmdSpreadsheet_Click()
   Dim errNumber As Integer
   Dim errDescription As String
   Dim errSource As String
   Dim strMsg As String

   On Error GoTo ErrorHandler
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
   "tblPublisher", "C:\My Documents\Publisher.xls"
   Exit Sub
ErrorHandler:
   strMsg = "Error number is: " & Err.Number & vbCrLf
   strMsg = strMsg & "Error message is: " & Err.Description & vbCrLf
   strMsg = strMsg & "Error source is: " & Err.Source
   MsgBox strMsg, vbOKOnly + vbCritical, "Error"
   Resume Next
End Sub

The message box this code creates provides you, the developer, with a lot of information about the reason that errors occur; you'll find this type of code in error handlers very useful while you're optimizing an application. However, this type of information is not meaningful to people who use your application. For those people, you need to employ a different strategy: code that identifies possible errors and suggests a remedy for each one. This involves the use of the Select Case control structure. .

The Select Case control structure allows you to identify specific errors by their error numbers and to write code that's appropriate for each type of error. Listing 5 is a skeleton example.

Listing 5  Example of Using the Select Case Control Structure to Identify Errors

Private Sub cmdSpreadsheet_Click()
   On Error GoTo ErrorHandler
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
   "tblPublisher", "C:\My Documents\Publisher.xls"
   Exit Sub
ErrorHandler:
   Select Case Err.Number
      Case 58    'File already exists
         code to solve problem
      Case 61    'Disk full
         code to solve problem
      Case 68 'Device unavailable
         code to solve problem
      Case Else 'Other errors
         Code to solve other problems
   End Select
End Sub

This error handler uses the Select Case control structure to look for specific error conditions recognized by their error numbers, and provides individual solutions for each type of error. You can use this type of error handler to provide specific solutions for the most likely problems and to provide a generic solution for any other problems.

Testing Your Error-Handling Strategies

After you've written an error handler, you need to test it to make sure it works correctly. It's impractical to set up all possible error conditions; fortunately, you don't have to. Instead, you can temporarily modify your code to make Visual Basic think that certain errors have occurred. This is known as raising errors. You specify which error you want to simulate by its error number.

For example, to raise error number 7 (out of memory), insert the statement

Err.Raise 7

somewhere in your code after the On Error GoTo statement and before the Exit Sub statement. When you run the code, your error handler does whatever it would do if that particular error had actually occurred.

Resuming Code Execution After an Error

With an error handler placed at the end of a procedure, immediately ahead of the End Sub statement, the procedure terminates after the code in the error handler has been processed, unless you specify otherwise.

You can use a Resume statement to control what happens after an error has been processed. A Resume statement can occur only within an error handler; an error occurs if you place a Resume statement elsewhere in your code. A Resume statement can be written in the three forms shown in Table 4. The explanations in that table apply when an error occurs in the same procedure as the error handler.

Table 4 Action of a Resume Statement When an Error Occurs in the Same Procedure as the Error Handler

Form

Explanation

Resume

Execution resumes at the statement in which the error occurred.

Resume Next

Execution resumes at the statement immediately following the statement in which the error occurred.

Resume label

Execution resumes at the line labeled label.

The effect of a Resume statement is somewhat different if an error trap is set in one procedure from which another procedure is called and the error occurs in the called procedure. Table 5 explains what happens in this case.

Table 5 Action of a Resume Statement When an Error Occurs in a Called Procedure

Form

Explanation

Resume

Execution resumes at the statement that last called out the procedure containing the error handler.

Resume Next

Execution resumes at the statement immediately following the statement that last called out the procedure containing the error handler.

Resume label

Execution resumes at the line labeled label, which must be in the same procedure as the error handler.

  • + Share This
  • 🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020