I've got a field in one table that will be updated around twice a week. When the old information is going to be deleted, I want it shifted into a different table so it can be stored perminantly for reference. Any ideas on how to do this?

Matt

To my mind you would have to do this programmatically. For example, if you are using a form to display the record to the user you could, in the background, save the values of all the displayed fields in variables. Then if the user changes a value, you could write the old values to some “archive” table.

How exactly would I do that?

Well to give you a start here’s one way (there are others). I put it together quickly so you better test it! :-)

Assume you have a table called tblProduct and one called tblProductArchive (that’s the one you use to save records). ProductID is an AutoNumber field.

The code below would go behind some kind of form bound to the tblProduct table.

Each time a new record is displayed (on the OnCurrent event) the values from that record are saved in some variables (Private strSupplier As String, etc). If the user presses the cmdArchive button on the form, the cmdArchive_Click() routine makes sure it’s not a record that was just added (checks blnNewRecord), if it’s not it takes the saved values and appends a new record to the tblProductArchive table.

Code for form:
>>>>>>>>>>>>>>>>>

Option Compare Database
Option Explicit

‘ Module level variables
Private blnNewRecord As Boolean
Private strSupplier As String
Private strProductName As String
Private lngProductID As Long

Private Sub cmdArchive_Click()

If blnNewRecord = False Then

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblProductArchive", dbOpenDynaset, dbAppendOnly)

' Append a record to the archive table
With rst
.AddNew

' If zero length string don't update field
If strSupplier <> "" Then
!Supplier = strSupplier
End If

If strProductName <> "" Then
!ProductName = strProductName
End If

!ProductID = lngProductID

.Update
.Close
End With

Set rst = Nothing
Set db = Nothing

MsgBox "Product archived"

Else
MsgBox "You can't archive a new record"
End If


End Sub

Private Sub Form_Current()
' Save the value in the current record in case we have to archive
' Don't save if it's a null value or error will occur

If Not IsNull(ProductID) Then

blnNewRecord = False

lngProductID = ProductID

If Not IsNull(Supplier) Then
strSupplier = Supplier
Else
strSupplier = ""
End If

If Not IsNull(ProductName) Then
strProductName = ProductName
Else
strProductName = ""
End If

Else
blnNewRecord = True
End If

End Sub