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]

Re: Catching SQL Errors

On Sep 25, 8:03 pm, iwasinnihon <iwasinni...@gm ail.comwrote:

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.openr ecordset(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]

Re: Catching SQL Errors

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_N ote) 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.Execu te sql, dbFailOnError
Me.Add_Note = Null
Me.Notes.Requer y
End If
Me.Add_Note.Set Focus

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]

Re: Catching SQL Errors

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_N ote) 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.Execu te sql, dbFailOnError
Me.Add_Note = Null
Me.Notes.Requer y
End If
Me.Add_Note.Set Focus
>
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@comcast.net]

Re: Catching SQL Errors

On Sep 25, 10:03 pm, iwasinnihon <iwasinni...@gm ail.comwrote:

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.