Turning Off Error Handling

Error traps that are enabled in a procedure are automatically disabled once the procedure is exited. However, you may want to turn off an error trap in a procedure while the code in that procedure is still running. To turn off an enabled error trap, use the On Error GoTo 0 statement. After Visual Basic runs this statement, errors are detected but not trapped within the procedure. You can use the On Error GoTo 0 statement to turn off error handling anywhere in a procedure , even within error-handling code.

For example, the following Sub procedure accepts an SQL string value and performs an action query based on that text. The On Error Resume Next statement handles any errors generated by the first Delete method of the QueryDefs collection if, for example, TempQuery doesn’t exist. The On Error GoTo 0 statement turns error handling off so that errors generated by any subsequent statements are revealed to the user.

Sub PerformSQLQuery(strSQLText)
	Dim dbs As Database, qdfSQLQuery As QueryDef

	On Error Resume Next
	Set dbs = DBEngine(0)(0)
	dbs.QueryDefs.Delete "TempQuery"
	dbs.QueryDefs.Refresh
	On Error GoTo 0
	Set qdfSQLQuery = dbs.CreateQueryDef("TempQuery", strSQLText)
	qdfSQLQuery.Execute
	qdfSQLQuery.Close
	dbs.QueryDefs.Delete "TempQuery"
	dbs.QueryDefs.Refresh
End Sub

Note   An On Error GoTo 0 statement turns off the most recently enabled error handler, not all error handlers. If you have an error handler for an entire procedure and also use inline error handling for a part of that same procedure, the On Error GoTo 0 statement disables the On Error Resume Next statement and the procedure’s error handler is called once again.