Access, Seeking Help

[tedward]

[FONT=&quot]I have an access 2010 table which has several hundred items. I use a control button when I need to delete any of the items from a table titled MainTable. However, instead of deleting the item(s) I would like a control button to remove the item(s) from the MainTable and place it in a new table I have titled DeletedItemsHistory. Any help will be much appreciated.[/FONT]

 

[VilaRestal]

You would copy it first and then delete as before.

I don't know what fields are present but lets say that in both tables have the same fields in the same order and there is an ID field that is the primary key (it's a number field and not autonumber in the DeletedItemsHistory table).

CurrentDb.Execute "INSERT INTO DeletedItemsHistory SELECT * FROM MainTable WHERE ID = " & Me.ID

will copy the current record into the DeletedItemsHistory table.

You'd then delete the record as before.

However, some error handling is needed. You don't want to delete until you're sure the copy has succeeded.

On Error Resume Next
Err.Clear
CurrentDb.Execute "INSERT INTO DeletedItemsHistory SELECT * FROM MainTable WHERE ID = " & Me.ID
If Err Then
    Debug.Print Err.Description
    MsgBox "Deletion failed"
Else
    RunCommand acCmdDeleteRecord
End If

 

[VilaRestal]

That design isn't perfect though. You'd actually want the ID field to not be the primary key in DeletedItemsHistory table. It should have its own autonumber primary key. That would allow duplicate IDs which would be possible as IDs get removed from MainTable they could get used again and then deleted again (a different record but with the same ID).

In that case the fields of the two tables aren't the same so you will need a more specific SQL Insert statement:

Dim strFields as String
strFields = "ID, Field1, Field2, Field3, Field4"
CurrentDb.Execute "INSERT INTO DeletedItemsHistory (" & strFields & ") SELECT " & strFields & " FROM MainTable WHERE ID = " & Me.ID

 

[tedward]

Thanks for the reply. I will see if this will work. Both tables will contain the same data.

 

[tedward]

Thank You. I will see if I am experienced enough to make this work. I deleted the extra post.

 

[Simon_MT]

I would still just set a flag to Deleted rather than moving records to another table.

Simon

 

[Pat Hartman]

I agree with Simon. If you hare saving the record in history because you think you may want to recover it, using a flag is a better solution and easier to implement if you start out that way. If you have a lot of queries already built, it will be a pain because you'll have to modify them to select only active records. But, you only have to do it once and if you have a newer version of Access, there is a cross reference available to tell you where each object is used.