Try Catch exceptions ??

I do most of my programming in vb.net and in a lot of cases I swallow the GUI error message in the Try Catch End Try loop and write to a table or log file and report a more friendly warning back to the user.

I need to know how to do the equivalent in VBA.

My vb.net method

Try

Catch oex As OleDbException
          messagebox.show("blah blah blah")
Catch ex As Exception
          messagebox.show("blah blah blah")
Finally
   If dc.State = ConnectionState.Open Then dc.Close
End Try


Okay, now onto my VBA code I'm trying to add a similar method to to trap on whether the update command was successful or not.

Dim Connection As New ADODB.Connection
Dim RecordSet As New ADODB.RecordSet
Dim sqlUpdate As String

sqlUpdate = "Update Material Set Matl_Desription ='" & descr & "' Where Material='" & matl & "'"

Connection.Open ConnectionString
RecordSet.Open sqlUpdate, Connection, adOpenStatic, adLockReadOnly

Connection.Close: Set Connection = Nothing


I'm trying to catch errors if the update statement has a problem in order to create an audit trail for a series of update statement my program will be running.
JMO9966Asked:
Who is Participating?
 
appariConnect With a Mentor Commented:
you have to use on error goto statement as follows

on error goto ErrX
Dim Connection As New ADODB.Connection
Dim RecordSet As New ADODB.RecordSet
Dim sqlUpdate As String

sqlUpdate = "Update Material Set Matl_Desription ='" & descr & "' Where Material='" & matl & "'"

Connection.Open ConnectionString
RecordSet.Open sqlUpdate, Connection, adOpenStatic, adLockReadOnly

Connection.Close: Set Connection = Nothing

goto safeExit

ErrX:
  ' handle the errors here
  resume safeExit

safeExit:
   exit sub/exit function
0
 
jjardineConnect With a Mentor Commented:
you would need to look at the On ERROR Resume Next  or ON Error GOTO  Label    settings.

DIM Connection as New ADODB.Connection
Dim RecordSet as New ADODB.Recordset
Dim sqlUpdate as String

sqlUpdate = "...."
ON ERROR GOTO Error_Handler
Connection.Open ConnectionString
RecordSet.Open ....

Exit Sub
Error_Handler:   ' Handle your errors here.    Should be an Err.Number,  Err.Description  etc.
Connection.Close
End Sub
0
 
carlsiyConnect With a Mentor Commented:
'VBA sample same as VB 6
Private Sub DivideByZero()
    On Error GoTo DivideByZero_Err
    Dim intNumber As Integer
    intNumber = 1
    MsgBox "Here's a common error: " & intNumber / 0
  DivideByZero_End:
    Exit Sub
  DivideByZero_Err:
    Select Case Err.Number
        Case 11 ' Division by zero.
            MsgBox "You can't divide " & intNumber & " by 0."
        Case Else ' Unanticipated error.
            MsgBox "Error " & Err.Number & " in procedure DivideByZero: " & _
                Err.Description & "."
    End Select
    Resume Next
 End Sub

' Visual Basic .NET Equivalent
Private Sub DivideByZero()
    Dim intNumber As Integer = 1
    Try
        intNumber /= 0
        MsgBox("Here's a common error: " & intNumber)
    Catch e As System.OverflowException
        MsgBox("You can't divide " & intNumber & " by 0.")
    Catch e As System.Exception ' Unanticipated error.
        MsgBox("Error in " & e.Source & ": " & e.Message)
    Finally
        ' Recovery code goes here.
    End Try
End Sub
0
 
JMO9966Author Commented:
Thanks guys!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.