Record Is Deleted - Error 3167

[BenSC]

Dear All,
I have a form "frmDevtargets" with a subform "frmTask". These forms are based on "tblDevTargets" and "tblTask" respectively. Before opening the form I create a backup copy of each table so if the user chooses not to save their changes I can restore the previous data from the backup tables.
When the user closes the form via a command button and chooses not to save their changes I close "frmDevTargets", delete the contents of "tblDevTargets" and "tbltask" using an SQL statement and import the previous data from the backup tables using another SQL statement.
However, I get an error message saying that the record has been deleted, which is an error code 3167. I have attached code to the On Error property of each form so can see that I'm getting an error code of 0 from "frmTask" when the form is closed, but the Record Is Deleted message appears in addition to this.
When I exit the form normally without deleting the contents of "tblDevTargets" I don't get any error messages. When I delete the contents of "tblDevTargets" from a button on my main menu form I also don't get any error messages.

Can someone please help as I've tried everything I can think of to get rid of, or at least trap, this error.

Thanks

Ben Cooper

 

[Tranman]

Ben,
When you open a form and populate it with data, the data you are seeing is not the data that is in the table, but is actually the data from the form's recordset, which is a copy of the data from the form's source table or query. Every data item in the recordset has a "hook" into the actual underlying data.

When you change the data on the form, the recordset updates the underlying data and everything is fine. However, when you delete the underlying data and replace it with a copy of the original, unaltered data, the hooks still point to the the first data (which has been deleted), thus, your "record has been deleted" message.

I fought a similar problem to yours a long time ago, and the way I solved it (it was ugly), was that every time I made a change on the form, I saved off a copy of the row that was changed, and if the user decided not to apply the changes, I patched the data in the recordset back to what it was before the changes, then the recordset was able to update the underlying data back to its original values. Like I said, it was ugly, but it was a solution.

If you decide to do something like this, you'll want to read up about the RecordsetClone object, too.

Tranman