DoCmd.RunSQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way for me to capture the execution status of DoCmd.RunSQL ? I
want to trap the execution status to handle it appropriately.

Thanks in advance.
 
Use your error handling in VBA. If it completes successfully, there will be
no error. If an error occurs, your error handling will trap it and you can
take the appropriate action there.
BTW, I recommend
Currentb.Execute
instead of RunSQL. It is faster and you don't have to set the warning
messages.
 
On Error GoTo will do. Or, you can check for the error number after the
command:

On Error Resume Next
Docmd.RunSQL strSQL
If Err.Number <> 0 Then
'Some Error Occurred
End If
 
I'm specifically trapping error when inserting duplicate record. The
err.number returned was 0.
 
Duplicate records are allowed unless you have unique indexes set up in table
design, so that is not an error as far as Jet is concerned. If you want to
avoid duplicate records, you need an index that doesn't allow duplicates.
 
I created the table and had indexed uniquely. When I puposely inserted a dup
record and it errored out but I just don't like the system message. So I'm
trying to customize the message by setting the "setwarnings" to false and
throw more understandable message.
 
I would not use the Set Warnings.
The typical way to do that is to use a DLookup in the Before Update event of
the control to test for the existance of the value:

If Not IsNull(DLookup("[UniqueFieldName]", "TableName", _
"[UniqueFieldName] = " & Me.txtSomeControl)) Then
MsgBox "This Value Already Exists"
Cancel = True
End If
 
Klatuu said:
On Error GoTo will do. Or, you can check for the error number after the
command:

On Error Resume Next
Docmd.RunSQL strSQL
If Err.Number <> 0 Then
'Some Error Occurred
End If
Click to expand...

You won't get an error with docmd.runSQL. That's why
currentdb.execute strSQL, dbfailonerror
is preferred.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
JJ said:
I'm specifically trapping error when inserting duplicate record.
Click to expand...

Why not design your query or your code so that you don't attempt to
insert duplicate records in the first place?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Back
Top