Deleting Records

[Nathan Bloom]

Hi,

I have a data entry form (access 2000) that also allows the user to add,
update, and delete records from the form. The Delete action is carried out
in an event procedure and has the following code:

Private Sub Command28_Click()
On Error GoTo Err_Command28_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Command28_Click:
Exit Sub

Err_Command28_Click:
MsgBox Err.Description
Resume Exit_Command28_Click

End Sub

Is there some code that I can add to this procedure to save the record in
another table before actually deleting the record. So, if it is
inadvertently deleted it is not lost forever.

Thanks in advance for any help offered.
Nathan Bloom

[Salad]

Nathan Bloom wrote:

Hi,

I have a data entry form (access 2000) that also allows the user to add,
update, and delete records from the form. The Delete action is carried out
in an event procedure and has the following code:

Private Sub Command28_Click()
On Error GoTo Err_Command28_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Command28_Click:
Exit Sub

Err_Command28_Click:
MsgBox Err.Description
Resume Exit_Command28_Click

End Sub

Is there some code that I can add to this procedure to save the record in
another table before actually deleting the record. So, if it is
inadvertently deleted it is not lost forever.

Thanks in advance for any help offered.
Nathan Bloom

Probably the easist way to fix this is to add a field called
ActiveInactive, default value True.

Then create a query on this table for all fields. Set the filter to
True in the query so it only shows active records. Then change the
recordsource for forms and reports that uses the table to the query.
Instead of deleting a record you tag the activeinactive field to false.

Other than that, create a duplicate table. In the new duplicate, change
the autonumber field, if you have one, to Long. Then create an append
query. Prior to deleting, call the append query to update the duplicate
table. You might want to add a datetime field to the duplicate with the
default value of =Now(). That way you know when it wase deleted. Maybe
add a user field too if you want to know who deleted the record if you
have security on.

[Nathan Bloom]

Thank you for the information. I do have security on the database. I think
adding a date field and a user field would also be beneficial. Could you
please provide me with information on how to capture the user that has
performed the delete?

Again, thabks in advance...
Nathan Bloom
"Salad" <oi*@vinegar.com> wrote in message
news:pr*******************@newsread1.news.pas.eart hlink.net...

Nathan Bloom wrote:

Hi,

I have a data entry form (access 2000) that also allows the user to add,
update, and delete records from the form. The Delete action is carried out in an event procedure and has the following code:

Private Sub Command28_Click()
On Error GoTo Err_Command28_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Command28_Click:
Exit Sub

Err_Command28_Click:
MsgBox Err.Description
Resume Exit_Command28_Click

End Sub

Is there some code that I can add to this procedure to save the record in another table before actually deleting the record. So, if it is
inadvertently deleted it is not lost forever.

Thanks in advance for any help offered.
Nathan Bloom

Probably the easist way to fix this is to add a field called
ActiveInactive, default value True.

Then create a query on this table for all fields. Set the filter to
True in the query so it only shows active records. Then change the
recordsource for forms and reports that uses the table to the query.
Instead of deleting a record you tag the activeinactive field to false.

Other than that, create a duplicate table. In the new duplicate, change
the autonumber field, if you have one, to Long. Then create an append
query. Prior to deleting, call the append query to update the duplicate
table. You might want to add a datetime field to the duplicate with the
default value of =Now(). That way you know when it wase deleted. Maybe
add a user field too if you want to know who deleted the record if you
have security on.

[Larry Linson]

"Nathan Bloom" wrote

I do have security on the database. I think
adding a date field and a user field would
also be beneficial. Could you please provide
me with information on how to capture the
user that has performed the delete?

If you only allow users to update from Forms, never directly from tables or
queries, you can get the current Access userid from the CurrentUser
function, but if you don't have security applied, they'll all read "Admin".

If your users have to log in to the network, then check the API call at
http://www.mvps.org/access/api/api0008.htm to get the network user id.

If your users don't have to log in anywhere or you let them update directly
from queries or datasheet view of the tables, then you are out of luck.

Larry Linson
Microsoft Access MVP