Delete record, without deleting

  • Thread starter Thread starter Larry
  • Start date Start date
L

Larry

I have a need to stop records from being deleted from the database (for
real) but still allow the user to delete the record if needed. This is
to keep all history, but not show the data that has been "deleted".

What I did was add a DELETED field to the table, then create a query
that gets all records that have not been deleted. The form is then
based on this query, as will all reports and such.

If the user attempts to delete a record, I trap it in the On Delete
event and cancel the delete, then set the delete flag to true.

I then try to perform a requery (Me.Requery) but I get an error
"Reserved Error" when that statement is executed.

I thought it might be because the record being shown had been marked
for deleted and couldn't be shown after the requery, so I put a goto
next record before performing the requery, but that didn't help.

Any thoughts?

TIA
 
Why don't you take away the user ability to "delete" and have them instead
mark your flag to remove the record?

I would not give my user's delete privileges if they should not be deleting.
 
Because they want to "delete" records, but they need to keep a history
of what has happend in the database. By doing this via the On Delete
event, I hope to catch any method of deleting the record they can try.

It give the user the feeling they are deleting the record, but the
admins in charge of the database, the ability to keep those deleted
records.
 
I have a need to stop records from being deleted from the database (for
real) but still allow the user to delete the record if needed. This is
to keep all history, but not show the data that has been "deleted".

What I did was add a DELETED field to the table, then create a query
that gets all records that have not been deleted. The form is then
based on this query, as will all reports and such.

If the user attempts to delete a record, I trap it in the On Delete
event and cancel the delete, then set the delete flag to true.

I then try to perform a requery (Me.Requery) but I get an error
"Reserved Error" when that statement is executed.

I thought it might be because the record being shown had been marked
for deleted and couldn't be shown after the requery, so I put a goto
next record before performing the requery, but that didn't help.
Click to expand...

Please post your code, and indicate what version of Access you're
running.

I'd suggest not providing the user with any way to delete the record
other than a command button (which just sets the Deleted field to
True) or even just a Delete checkbox bound to the field.

John W. Vinson[MVP]
 
I am using a checkbox, behind the scenes, so even if I did JUST the
checkbox, I figure I would have the same problem on ReqQuery.

Doing it this way, it gives the users the illusion they are deleting
the record, just like any other database, but I'm protecting them in
the background.


Access 2003

Private Sub Form_Delete(Cancel As Integer)
Dim intRC As Integer

Cancel = True
intRC = MsgBox("Are you sure you want to delete this interface?",
vbYesNo, "Delete Confirmation")
If intRC = vbYes Then
Me.chkDeleted = True
DoCmd.GoToRecord , , acNext
Me.Requery
End If
End Sub
 
I would expect you to have problems if you try to requery in the
middle of the OnDelete event. Logically, the current record is in an
undefined state, and the Delete needs either to complete or to be
properly cancelled before you can validly requery the data set. If you
just set up a delete button to set your Deleted flag and requery,
however, there should be no problem of this sort. To prevent anyone
using any other route to real record deletion, you can leave code in
your OnDelete Event that _always_ cancels the Delete


I have a need to stop records from being deleted from the database (for
real) but still allow the user to delete the record if needed. This is
to keep all history, but not show the data that has been "deleted".

What I did was add a DELETED field to the table, then create a query
that gets all records that have not been deleted. The form is then
based on this query, as will all reports and such.

If the user attempts to delete a record, I trap it in the On Delete
event and cancel the delete, then set the delete flag to true.

I then try to perform a requery (Me.Requery) but I get an error
"Reserved Error" when that statement is executed.

I thought it might be because the record being shown had been marked
for deleted and couldn't be shown after the requery, so I put a goto
next record before performing the requery, but that didn't help.

Any thoughts?

TIA
Click to expand...

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Back
Top