Is there some library that handles errors?. Form some strange reason error messages are displayed even when I write error trapping codes.

One line codes as On Error Resume Next still display error


The code below still displays error and error number as 3144


Private Sub Form_Unload(Cancel As Integer)

    On Error GoTo Form_Err
    DoCmd.SetWarnings False
    strSQL = "UPDATE tblSystem SET tblSystem.BankID = " & Forms!frmTransactions!BankID
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

Exit_Form_Err:
    Exit Sub


Form_Err:

    Select Case Err.Number
        Case 3144
            Resume Exit_Form_Err
        Case Else
            msg = MsgBox(Err.Number)
            Resume Exit_Form_Err
    End Select

End Sub

Thanks for any suggestions

You can learn a bit more about the error by using the description property and include it in the message. The err.Number is the default property of the error so it is the same as err. I'm not seeing the return from the MsgBox function dimensioned nor the strSql variable so it looks like you don't have option Explict turned on in the code behind the form. The cause of the error 'Syntax error in UPDATE statement.' that is reported could be because the control reference is invalid or there is a null in the control. You would be better served to set the cancel parameter true in the error handler and advise the user that the value in the control is invalid if it is null.

A good way to test the sql statement is to debug.print it in the error handler so you can paste it into the SQL view of a new query to see what the problem is.

In addition to showing the err.Description in the message, I like to insert the procedure name so it is easy for a user to report the error. I would also place any cleanup code in the Exit_Form_Err portion of the routine. Tyically this is where you would have cleared the hourglass set at the start of a longer running query as well as cleared all local object references (or as in my commented out example, reset the system progress bar). I like to use a resume 0 which you can uncomment when the code stops for an error. That causes the code to resume at the line that raised the error making it much easier to identify cause of the problem.

Exit_Form_Err:
    On Error Resume Next
    'SysCmd acSysCmdRemoveMeter or close and set to nothing, & _ cancel hourglass
    Exit Sub
Form_Err:
    With Err
        Select Case .Number
            Case 3144
                Resume Exit_Form_Err
            Case Else
                MsgBox .Number & vbCrLf & .Description, & _
                    vbInformation, "Error - Procedurename"
        End Select
    End With
    'Resume 0
    Resume ExitRoutine
Exit Sub

The best solution to your problem is not to use docmd to run an update query. Essentially that is code calling a macro to do what can be done and handled in code directly. Macros have no capability to trap error so Access provides the setwarnings connection to macro methods. If you use the Execute method of a database object, you get no errors and you don't need code to call a macro to turn warnings on and off either.

Private Sub Form_Unload(Cancel As Integer)

    Dim strSQL As String
    Dim db As Database

    On Error GoTo Form_Err
    Set db = CurrentDb
    strSQL = "UPDATE tblSystem SET tblSystem.BankID = " & Forms!frmTransactions!BankID
    db.Execute strSQL
    If Not(db.RecordsAffected) Then 'recordsaffected is a number_ 0 is false therefore failure
        If MsgBox("Database not updated. Check valid" & _
          "entry in 'textbox'. Do you wish to close the " & _
          "form anyway?", vbYesNo) = vbYes Then
        Else
            Cancel = True
        End If
Exit_Form_Err:
    On Error Resume Next
    db.Close
    Set db = Nothing
    Exit Sub

Form_Err:
    Select Case Err.Number
        'Case 3144 - no longer an issue, add others as required
        Case Else
            MsgBox .Number & vbCrLf & .Description, & _
                vbInformation, "Error - Procedurename"
    End Select
    'Resume 0
    Resume Exit_Form_Err
End Sub

As well as the sound advice given above check in the options for code editing. Do you have 'Break on all errors' chosen instead of 'Break on unhandled errors'?

Joe

--

Joe

Thanks for the insight Joe. I have Opiton Explicit set to on and I use msg as Global variabel in a module. That is why you did not see a Dim line.

I am wondering if I have to use DAO to run the procedure you gave me. If so, is it possible for you to rewrite it with ADO since I am trying to write all my apps with ADO. I am new to Access and since DAO is on its way out I did not go into learnind it.
Thanks

In ADO you don't have the CurrentDb function to return a database against which to run the .Execute method. Instead you use the .Execute method of a Connection object that you create and set pointing to the appropriate data provider and path to database. The online help is pretty clear.

Just for the record, Access still uses DAO internally for bound recordsets and list and combo box row sources. Things like Docmd and domain aggregate functions do also. Why? Because the ADO and ADOX are more generic in order to work against a broader range of data providers. All the extra weight and overhead of being able to work against various data providers adds a layer of translation code between Access and the backend data whereas DAO uses Jet natively which is the direct mdb data provider. ADO is like having a ballerina wear a pair of work boots over her slippers. She can go to a construction site with the boots, but her dancing on the stage suffers. If she's going to dance on a stage, she does better without the extra layer of indirection but the boots allow her to go to job sites where she may be able to do some work, but she's somewhat out of her element in those environments.

ADO adds some versatility because you can hit Oracle or SQL Server backends and these can provide data more efficiently, but Access is not always the best front end for industrial strength disconnected transaction based data applications. If you want to upgrade to those kinds of backends, you would be well advised to work with ADO. However, your typical Mom & Pop to 20 local user 50 Megabyte databases (or fewer user/less size) are going to have to give up some performance to use ADO.

Ciao
Jurgen Welz
Edmonton AB Canada
[email protected]