Running SQL

  • Thread starter Thread starter TeeSee
  • Start date Start date
T

TeeSee

The following code compiles but when CmdButton is clicked I get an
error with DoCmd.RunSQL strSQL hilited in the debug window. Please
advise what I might be missing. Thanks as always!


Private Sub cmdDelete_Click()
Dim strSQL As String
strSQL = "Delete * From tblCDMRvalues WHERE
((tblCDMRvalues.ynDelete)=Yes);"
DoCmd.RunSQL strSQL
End Sub
 
TeeSee said:
The following code compiles but when CmdButton is clicked I get an
error with DoCmd.RunSQL strSQL hilited in the debug window. Please
advise what I might be missing. Thanks as always!


Private Sub cmdDelete_Click()
Dim strSQL As String
strSQL = "Delete * From tblCDMRvalues WHERE
((tblCDMRvalues.ynDelete)=Yes);"
DoCmd.RunSQL strSQL
End Sub
Click to expand...


What error do you get? Aside from the line-wrapping of the assignment to
strSQL, which I assume was created by the newsreader, I don't see anything
obviously wrong with that code. Anytime you want help with an error, you'll
save everyone a lot of time by posting what the error message (and number,
if one is displayed) actually is.
 
What error do you get?  Aside from the line-wrapping of the assignment to
strSQL, which I assume was created by the newsreader, I don't see anything
obviously wrong with that code.  Anytime you want help with an error, you'll
save everyone a lot of time by posting what the error message (and number,
if one is displayed) actually is.
Click to expand...

Sorry ...I misinformed. It appears this code runs correctly the error
I get is after I get the Access generated info box asking if okay to
proceed with the delete. If I answer YES all is ok but if I answer NO
i get run time error 2501 "The runSQL action was cancelled". Could you
now then please advise how to properly handle this situation.

Thanks again for the response.
 
TeeSee said:
Sorry ...I misinformed. It appears this code runs correctly the error
I get is after I get the Access generated info box asking if okay to
proceed with the delete. If I answer YES all is ok but if I answer NO
i get run time error 2501 "The runSQL action was cancelled". Could you
now then please advise how to properly handle this situation.
Click to expand...


That "error" is always raised when your code requests an action that is
subsequently cancelled. In this case, your code requested the execution of
a delete query, but then it was cancelled by the user. Assuming you want to
allow the user the ability to cancel the action, you need to use
error-handling to trap for the error that is raised when they do, and ignore
it. For example,

'----- start of code -----
Private Sub cmdDelete_Click()

On Error GoTo Err_Handler

Dim strSQL As String

strSQL = "Delete * From tblCDMRvalues WHERE ynDelete=Yes;"

DoCmd.RunSQL strSQL

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
End If
Resume Exit_Point

End Sub
'----- end of code -----

Note that, the above code represents a simple case where you don't have
anything else to do in the procedure after the RunSQL is cancelled, so
execution resumes from the error-handler at Exit_Point, regardless of the
exact error. There could easily be more elaborate situations, where you
resume at any of several locations depending on the nature of the error or
where it occurred.
 
Back
Top