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).
Code:
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.