Catch Error 2501

[fperri]

Hello,

I have a form in Access for users to do updates from. I'm trying to catch the error 2501 that pops up if they say cancel to the prompt that tells them how many records they are going to update and asks them if they want to continue or cancel.

Here is my code, but it is not working.....

Err_btnUpdate_C lick:
If Err.Number = 2501 Then
MsgBox "Update Cancelled.", vbOKOnly
Resume Exit_btnUpdate_ Click
Else
MsgBox "Error: " & Err.Number & " Description: " & Err.Description , vbOKOnly
End If

Also, if there isn't a way to get around this, and I just turn the warnings off......is there a way to get the number of records that are going to be updated from an SQL update query executed in the code so that I could display this to the user?

Thank you!

~ Franccesca

[puppydogbuddy]

Hello,

I have a form in Access for users to do updates from. I'm trying to catch the error 2501 that pops up if they say cancel to the prompt that tells them how many records they are going to update and asks them if they want to continue or cancel.

Here is my code, but it is not working.....

Err_btnUpdate_C lick:
If Err.Number = 2501 Then
MsgBox "Update Cancelled.", vbOKOnly
Resume Exit_btnUpdate_ Click
Else
MsgBox "Error: " & Err.Number & " Description: " & Err.Description , vbOKOnly
End If

Also, if there isn't a way to get around this, and I just turn the warnings off......is there a way to get the number of records that are going to be updated from an SQL update query executed in the code so that I could display this to the user?

Thank you!

~ Franccesca

Franccesca,

You did not explain what you mean by your code "is not working" in this context.

However I think your problem might be that the 2501 error is an openForm failure and does not necessarily have anything to do with clicking the Cancel button in a dialog box, as the 2501 error message states. Actually, I don't believe clicking the cancel button generates an error at all.

So if you are getting a 2501 error OpenForm failure), the first thing I would do is check to see if the dialog (prompt) box that you are calling from btnUpdate is in fact opening. One thing to check is to see if you are able to open the dialog box by itself without code.

If you are not getting a 2501 error, but are trying to trap it because the error message has led you to believe the 2501 error is related to the cancel button....then I would place the code dealing with the click of the cancel button as a separate button click event and code it accordingly.

[fperri]

Sorry,

The form that this is all comming from is a form that allows users to select filters and apply changes to the data in the database. So on the form there is a button for "Update" that they can click once they've selected all their filters and entered in the change. In the update button's click event I execute an SQL statement using the parameters they gave me using:

DoCmd.RunSQL sqlTxt

sqlTxt is my SQL string.

I want the user to be able to see how many records they are going to update so I left the warnings on which makes the message box pop up telling them how many records they are going to update. This is the message box I was talking about.

If the user changes their mind and clicks cancel they get a runtime error 2501 saying "The sql action was cancelled." and it gives you the option to end or debug.

I don't want the user to see this message. I just wanted the code to stop executing so the code I posted above I put in my update button's click event and it was my attempt at catching this error so that I could tell it to exit the sub, that is the part that isn't working, because the runtime error 2501 still pops up, but I'm probably going about it all wrong.

I could probably create a recordset first from the filters to find out how many records are going to be updated, turn off the warnings, use my own textbox with a yes or no, telling them how many records they are going to update - and if they say no exit the sub - and if they say yes run the update query - and do it that way, but I'm thinking that there has got to be a better way.

[puppydogbuddy]

Sorry,

The form that this is all comming from is a form that allows users to select filters and apply changes to the data in the database. So on the form there is a button for "Update" that they can click once they've selected all their filters and entered in the change. In the update button's click event I execute an SQL statement using the parameters they gave me using:

DoCmd.RunSQL sqlTxt

sqlTxt is my SQL string.

I want the user to be able to see how many records they are going to update so I left the warnings on which makes the message box pop up telling them how many records they are going to update. This is the message box I was talking about.

If the user changes their mind and clicks cancel they get a runtime error 2501 saying "The sql action was cancelled." and it gives you the option to end or debug.

I don't want the user to see this message. I just wanted the code to stop executing so the code I posted above I put in my update button's click event and it was my attempt at catching this error so that I could tell it to exit the sub, that is the part that isn't working, because the runtime error 2501 still pops up, but I'm probably going about it all wrong.

I could probably create a recordset first from the filters to find out how many records are going to be updated, turn off the warnings, use my own textbox with a yes or no, telling them how many records they are going to update - and if they say no exit the sub - and if they say yes run the update query - and do it that way, but I'm thinking that there has got to be a better way.

FPerri,
As I previously stated the 2501 error is an OpenForm failure error, which has nothing to do with cancelling the update as far as I can tell. Unless you are cancelling the opening of a form, you probably have the wrong error #. Nonetheless, as you stated, you can probably suppress the error message by turning the Warnings off.

In regards to your other question about the number of records that are about to be updated......yo u can copy and paste the SQL string into the sql view of an Access query; then save the query as qryUpdate, and use it as the domain in a Dcount expression that would be the control source of a textbox on your form as shown:

= Dcount(“[KeyField]”, “qryUpdate”)

For more info on the Dcount function see this link:

http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx