Bob, please reread my message above.

I assert - pretty strongly! - that what you are asking to do (moving a
record from a main table to an archive table) SHOULD NOT BE DONE. It's
counterproductive; the yes/no field in the main table IS A BETTER
ALTERNATIVE.

That said... if you really, really want to do it that way anyhow, then
you are correct, an update query will not work.

Instead you will need to write VBA code to run an Append query
followed by a Delete query in succession. These should be wrapped in a
Transaction, to ensure that either both queries run successfully or
neither runs at all. The Append query could contain a calculated field
to append to the ArchiveDate field, defined as

ArchiveDate: Date()

I cannot write this query for you because I cannot see your table, I
do not know how you are selecting the record, and I do not know the
names of the fields in either table. But assuming you have the Append
and Delete queries written, referencing a control on the form to
select the desired record, the code for the button click event might
be:

Private Sub cmdArchive_Click()
Dim ws As Workspace ' need a workspace to do a Transaction
Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim prm As Parameter
Dim inTrans As Boolean
Set ws = DBEngine(0) ' current workspace
On Error GoTo Proc_Error ' trap any query errors
ws.BeginTrans
  iTrans = True
  Set db = CurrentDb
  Set qd = db.QueryDefs("appArchive") ' your archive append query
  For Each prm In qd.Parameters ' evaluate parameters
     prm.Value = Eval(prm.Name)
  Next prm
  qd.Execute dbFailOnError
  Set qd = db.QueryDefs("delArchive")
  For Each prm In qd.Parameters ' evaluate parameters
     prm.Value = Eval(prm.Name)
  Next prm
  qd.Execute dbFailOnError
  ' if all is well, commit the transaction
ws.Commit
Proc_Exit:
  Exit Sub
Proc_Error:
  If InTrans Then
      ws.Rollback
      MsgBox "Unable to archive record", vbOKOnly
  End If
Resume Proc_Exit
End Sub

                 John W. Vinson[MVP]