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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

appariCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jjardineCommented:
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
carlsiyCommented:
'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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.