Catching SQL Errors

[iwasinnihon]

How do I catch SQL errors in Visual Basic in Access? I would like to
display a message box if the sql errors out instead of it popping up
with the Debug/cancel box.

[Roger]

On Sep 25, 8:03*pm, iwasinnihon <[e-mail]:

How do I catch SQL errors in Visual Basic in Access? *I would like to
display a message box if the sql errors out instead of it popping up
with the Debug/cancel box.

use 'on error...'

on error goto fErr
strsql = "select * from tblCity..."
set rs = currentdb.openrecordset(strsql)
...
...
fExit:
set rs = nothing
exit sub

fErr:
debug.print err.description ' this will print the error message to you
can
' log to a text file
resume fExit

[iwasinnihon]

I tried what you said, but it runs the error everytime now. See code
below.

Private Sub Add_Note_Button_Click()
If IsNull(Me.Add_Note) Then

Else
Dim sql As String
sql = "INSERT INTO LPC_Notes VALUES('" & Me.Index_Number & "','" &
Me.Add_Note & "','" & Me.NoteDate & "');"
On Error GoTo AddError
CurrentDb.Execute sql, dbFailOnError
Me.Add_Note = Null
Me.Notes.Requery
End If
Me.Add_Note.SetFocus

Exit_Function:
Exit Sub

AddError:
MsgBox ("Invalid entry. Make sure you are not using any special
characters and resubmit.")

Resume Exit_Function
End Sub

[Salad]

iwasinnihon wrote:

I tried what you said, but it runs the error everytime now. See code
below.

Private Sub Add_Note_Button_Click()
If IsNull(Me.Add_Note) Then

Else
Dim sql As String
sql = "INSERT INTO LPC_Notes VALUES('" & Me.Index_Number & "','" &
Me.Add_Note & "','" & Me.NoteDate & "');"
On Error GoTo AddError
CurrentDb.Execute sql, dbFailOnError
Me.Add_Note = Null
Me.Notes.Requery
End If
Me.Add_Note.SetFocus

Exit_Function:
Exit Sub

AddError:
MsgBox ("Invalid entry. Make sure you are not using any special
characters and resubmit.")

Resume Exit_Function
End Sub

Do a
debug.print strSQL
You say it will error out. If not, comment out the On Error Goto line.
Now Go to the Debug/Immediate window and enter
? SQL
Who knows, change the name to strSQL. See reserved words at Allen
Browne's site
http://allenbrowne.com/AppIssueBadWord.html

[brucedodds]

On Sep 25, 10:03*pm, iwasinnihon <[e-mail]:

How do I catch SQL errors in Visual Basic in Access? *I would like to
display a message box if the sql errors out instead of it popping up
with the Debug/cancel box.

Are these errors that occur in a stored procedure in some other
database (like Oracle) that you call from VBA via ADO? If so, there
are protocols for handling them.