Do note that it's probably of zero benefit to have a table and an
archive table in the same database. There is no explicit limit on the
size of a table; the limit is on the entire size of the database, and
having the data in two tables instead of one will use MORE disk space,
not less. With proper indexing you shouldn't need to split the data
into two tables anyway. Could you consider just having a Yes/No field
[Archived]? Base your forms on queries selecting the appropriate value
of this field; on the form that lets you archive a record, you can set
the ArchivedDate field in the AfterUpdate event of the checkbox bound
to [Archived].
John W. Vinson[MVP]
OK I simplified things in hopes of understanding what the procedure is to
copy an entire record from one table to another with the same fields except
one. What I really wish to do is:
1) Prompt for a record to delete from the active table
2) move said record from the active table to the archive table (by move I
mean add to the archive table and delete from the active table).
3) add the archive date to said record (prompt for this as well)
I don't understand how and update query will do this. I don't see where the
data is being taken from the active table and moved to the archive table.
Please be as explicit as possible as I seem to be missing something obvious.
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]
