Copying a record, before deleting it

[avivit]

Hi,

I would like to copy a record to another table, before it is being deleted from a form, to a 2nd table with all it's content. Of course, only when the deletion is confirmed by the confirm delete box.
I would like to use VBA for that.

I guess I need to use the event
Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)

But then what?


P.s. - I need a table of the deleted records, not a form.

Thanks very much

 

[jrbarnett]

Firstly, you need to ensure that the backup table has all the appropriate fields with data type and size to accomodate the record to be deleted, then in your code, use something like:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)

  If MsgBox ("Are you sure you want to delete this record?", vbYesNo+vbQuestion) = vbYes Then
   ' copy the record across
   DoCmd.RunSQL "Insert Into DeletedRecordsTable (Field1, Field2, Field3, Field4 Select FieldA, FieldB, FieldC, FieldD From SourceTable Where PrimaryKeyField='" & Me!Fiedname & "'"
   Response = acDataErrContinue ' continue with the delete
  Else
   ' They chose No, so cancel the delete
   Cancel = True
  End If
End Sub

Obviously you will need to customise the insert statement to copy the data across, but that gives you the general idea of what to do.

John

 

[avivit]

Thanks.

Two questions:

1. How can I know (and keep) what is the CURRENT primaryKeyField?

2. As far as I understand, this will copy the fields from the form, and not from the table.
One of the fields I have to copy is a field that only exists in the table, and not in the form (ID=AUTOnumber), which is the priaary key.

Thanks very much

 

[PHV]

You should have all the info in the form's recordset.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244