By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

Catching SQL Errors

P: n/a
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.
Sep 26 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Sep 25, 8:03*pm, iwasinnihon <iwasinni...@gmail.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.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
Sep 26 '08 #2

P: n/a
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
Sep 29 '08 #3

P: n/a
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

Sep 29 '08 #4

P: n/a
On Sep 25, 10:03*pm, iwasinnihon <iwasinni...@gmail.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.
Oct 1 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.