Hello!
This code below worked great and copies my active record once I hit my command button from my current table to my archive and then deletes the record. I also added REQUERY to refresh screen. I can delete records 2 - unlimited. My issue however is in the next paragraph below the code example.
-------START CODE----------
Private Sub Delete_Test_Click()
Dim Msg, Style, Title
Msg = "Record archived and deleted!"
Style = vbOK + vbDefaultButton1
Title = "Operation Completed!"
Dim i As Integer
Dim rsOld As DAO.Recordset
Dim rsNew As DAO.Recordset
Set rsNew = CurrentDb.OpenRecordset("SELECT * FROM Deleted_Env_InventoryDB")
Set rsOld = CurrentDb.OpenRecordset("SELECT * FROM Env_InventoryDB WHERE(ID=" & ID & ")")
rsNew.AddNew
For i = 0 To rsOld.Fields.Count - 1
rsNew.Fields(i).Value = rsOld.Fields(i).Value
Next
rsNew.Update
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM Env_InventoryDB WHERE (ID=" & ID & ")"
DoCmd.SetWarnings True
rsNew.Close
rsOld.Close
Set rsNew = Nothing
Set rsOld = Nothing
Requery
Response = MsgBox(Msg, Style, Title)
Requery
End Sub
------------END CODE--------------
However I cannot delete record #1. It says that "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data entries and try again."
Only record #1 does this, nothing special in this record compared to #2 - whatever.
Ideas? - Thanks!
