Delete Records from Access database, error while deleting

Question

I have the following situation: I built an Access form with a subform (which records are linked to the records of main form via certain key). When I try to delete any record in the subform, I get the following message: “Access has suspended the action because you and another user tried to change the data” (approximate translation from German). Does anyone know how to delete those records from the subform (and, respectively, from the table behind the form).

Answer 1

If you are currently 'editing' the current form then it will not allow the action. Editing a record can sometimes be triggered by simply clicking inside a field, or other simple actions you wouldn't normally consider 'editing'.

This is usually avoided in Access by using the RunCommand method to undo any edits before deleting the record:

DoCmd.RunCommand acCmdUndo

Answer 2

Also check the "row locking mechanism" that you have. I haven't used Access in a while but I remember that you could use set that in the table properties. You can access those properties clicking in the famous "dot" in the upper left corner of the table to bring up its properties. Well if you're using Access, you know what I'm talking about.

Answer 3

samjudson suggested:

DoCmd.RunCommand acCmdUndo

You can also use Me.Undo, to undo the last edit to the form in which the code runs.

Or, Me!MySubForm.Form.Undo to undo the last unsaved edit in the subform whose subform control is named "MySubForm".

You can also use Me!MyControl.Undo to cancel the last edit to a particular control.

"DoCmd.RunCommand acCmdUndo" will apply the Undo operation to the currently selected object, but you won't know for sure whether it will apply at the control or form level. Using the commands I suggested completely disambiguates what gets undone.

Keep in mind, though, that Undo will not undo edits to a control after the control's AfterUpdate event has fired, or to a form after its AfterUpdate event has fired (i.e., the data has been saved to the underlying data table).

--
David W. Fenton
David Fenton Associates