Error 2501 while trying to delete a record

I have an Access database front-end and I'm trying to put a button on a form to delete the current record. I used the following VBA code to delete the record:

If Me.NewRecord Then
    Me.Undo
    Exit Sub
End If

DoCmd.RunCommand acCmdDeleteRecord

Me.Requery
Me.Refresh

When I run this on records that I inserted into the database with the form, It returns Run-time error '2501' on the DoCmd. However, if I run it on a record that had already existed in the database then the code completes as intended.

Additionally, no one else is accessing this database table yet and I only had the one form open.

When I went to delete them from the linked table manually in access I got the same error but I was able to delete them from the database using SQL Server Management Studio.

What would cause this to happen?

EDIT

I did some more investigating and found that I am unable to edit the new开发者_如何学编程 records in in the base table using access either. I get an error about the records being changed by another user.

Other than the recommendation to have a timestamp field in the table (SSMA assistant adds this to all tables when you use it to upsize from Access, and it's definitely something I'd recommend), I have some criticism of your code. I'd write it this way:

  If Me.NewRecord Then
     Me.Undo
  Else
     DoCmd.RunCommand acCmdDeleteRecord
     Me.Requery
  End If

The refresh is redundant after a requery, as you already have the most recent data.

Using Exit Sub is helpful for guard clauses on things that aren't mutually exclusive, but in this case you have an either/or -- either your going to delete an existing record or undo a new record. That can be handled within a single If/Then/Else block and then you have a single exit point for your subroutine, which is very helpful in case the code grows more complex in the future.

This is not the answer to your specific problem, but regarding the question title of getting Error 2501 while trying to delete a record, another situation where that happens is this:

You try to delete a row in one table that would orphan rows in another table (because the two tables are linked via a foreign key). SQL Server rejects the deletion and Access returns that vague 2501 error code.

In my case, I solved the issue by dropping and recreating each foreign key with ON DELETE CASCADE so that when a row in the "main" table is deleted via Access, SQL Server automatically deletes the corresponding rows in the each "detail" table.

See these questions for more detail on cascade delete:

• Good explanation of cascade (ON DELETE/UPDATE) behavior
• How do I use cascade delete with SQL Server?