Microsoft Access VBA Macro Programming

Instead, you add another field of Boolean type (Yes/No) to the underlying table called

Deleted. You then put your own Delete button on the form and execute a SQL update

statement to set the Deleted field to No:

CurrentDb.Execute ("update Customers set Deleted=True where ID=" _

& Me.ID)

This statement picks up the current ID using theMeobject for the form.

You will need to change your form query so that records are only included where the

Deleted field is False.

You also need to include the audit trail code from earlier on the Delete button, changing

the action from Edit to Delete in the concatenation.

Enhancing the Audit Trail

You can also add more granularity to your audit trail field by adding in the values of fields on

the form so that the audit trail shows a snapshot of what the user changed the values to:

Private Sub Form_AfterUpdate()

Dim RecSet As Recordset, Temp As Variant

Set RecSet = CurrentDb.OpenRecordset("select * from Customers where ID=" & Me.ID)

Temp = RecSet!AuditTrail

Temp = Temp & "Edit" & "|" & ReturnUserName & "|" _

& Now() & "|" & Me.First_Name & "|" & Me.Last_Name & "|" & Me.E_mail_Address

CurrentDb.Execute ("update Customers set AuditTrail='" & Temp & "' where ID=" _

& Me.ID)

End Sub

This can get messy if many fields are displayed on the form, but it does allow you to see

exactly what each user has done within the form. For some applications, you do need this

level of granularity.

292 Microsoft Access 2010 VBA Macro Programming