Delete button with archive Table

[tlyons]

Hey, I need some expert advice. I'm trying to create a delete button in a form which would delete a record from the current table and transfer it to a seperate archive table. Any suggestions would be much appreciated. Tks

[pbaldy]

Generally speaking you shouldn't move records around. I'd have a "status" field that you would simply mark as archived. That leaves all records in one table, where you can easily query current, archived or all records. If you really want to do it, you'd run an append query and then a delete query.

[Rawb]

I'm with Pbaldy on this one.

Unless you're dealing with performance or size issues with your database, I don't recommend deleting records from your Table. It's far better (and easier) to just add an "Archived" Yes/No field. For the Records you need to delete, just mark the field Yes.

If you absolutely have to (and, unfortunately, having your boss say "because I said so" is a valid reason :/), then you'd make a VBA function that does the following:

1. Opens both your "main" Table and the "archive" Table.
2. Copies the Record over to your "archive" Table.
3. Deletes the Record from your "main" Table.

If you're not familiar with VBA coding and have to go this route, then just let us know.

[Nixx1401]

can you post the relative VBA code for the above solution

Nixx1401

[Rawb]

I'm assuming you're asking for VBA code for the second option, since the first is extremely simple (just add 1 field to the Table and then Filter all your Queries where that field = True).

  On Error GoTo Error_Archive

  Dim work As Workspace
  Dim rstTable1 As DAO.Recordset
  Dim rstTable2 As DAO.Recordset

  Dim boolTransActive As Boolean
  Dim strCriteria As String

  boolTransActive = False

  Set work = DBEngine(0)

  ' Assumes Me!RecordID is the Record from Table1 that you want to archive in Table2.
  Set rstTable1 = CurrentDb().OpenRecordset("SELECT * FROM Table1 WHERE [ID_Field]=" & Me!RecordID, dbOpenDynaset)
  Set rstTable2 = CurrentDb().OpenRecordset("SELECT * FROM Table2 WHERE [ID_Field]=" & Me!RecordID, dbOpenDynaset)

  If rstTable2.RecordCount > 0 Then
    MsgBox "Error! This Record has already been archived! You will need to manually remove it from Table1."
    GoTo FunctionClosing
  End If

  work.BeginTrans
  boolTransActive = True

  ' Add the Record to the archival Table.
  With rstTable2
    .AddNew
    !Field1 = rstTable1("Field1")
    !Field2 = rstTable1("Field2")
    !Field3 = rstTable1("Field3")
    .Update
  End With

  ' Delete the Record from the original Table.
  With rstTable1
    .Delete
    .Update
  End With

  work.CommitTrans
  boolTransActive = False

:FunctionClosing:
  rstTable1.Close
  rstTable2.Close

  Set work = Nothing
  Set rstTable1 = Nothing
  Set rstTable2 = Nothing

  Exit Function

Error_Archive:
  If boolTransActive = True Then
    work.Rollback
  End If

  MsgBox "The following error occurred while attempting to archive Record " & CStr(Me!RecordID) & ", please contact your System Administrator." & _
         vbCrLf & vbCrLf & "'" & Err.Description & "'"

  Resume FunctionClosing

You'll need to replace the bolded text with whatever's appropriate for your Table setup. Also, this is aircode, so there may be a few minor typos/etc. in it.