I have a frmClients that is based on tblClients. Whenever a client is deleted from this form/table I would like for the record to be copied to a table in a different database. Despite prompting my users if they are sure they want to delete a client my users still do accidental deletions. I want to be sure a client’s record is still around in case my user did an acidental deletion.
I have listed below the code that I am using to copy the deleted record to a temporary table. However this code only work if the temporary table(tblDeletedClients) reside on the same database. I would like to change the code so that I can store the tblDeletedClients on a different database separated from the one my clients are using. How can I change the code to achieve this kind of set-up? Thank you.
Const conBackEndPath = “GmoreClientsData”
Private Sub Form_Delete(Cancel As Integer)
Dim wrk As Workspace
Dim DB As Database
Dim ctlSSN As Control
Dim qdf1 As QueryDef
On Error GoTo Form_DeleteErr
Set DB = DBEngine.Workspaces(0).OpenDatabase(conBackEndPath & “Clients_be.mdb”)
Set wrk = DBEngine.Workspaces(0)
Set ctlClientSSN = Me!txtClientSSN
Set qdf1 = DB.CreateQueryDef(“”, “INSERT INTO tblDeletedClients ” & _
“SELECT * ” & _
“FROM tblClients ” & _
“WHERE ClientSSN = ‘” & ctlClientSSN & “‘;”)
DoCmd.SetWarnings False
qdf1.Execute
DoCmd.SetWarnings True
Form_DeleteDone:
Exit Sub
Form_DeleteErr:
Select Case Err.Number
Case Else
MsgBox Err.Number & ” – ” & Err.Description
End Select
Resume Form_DeleteDone
End Sub
