Move Record from one table1 to table2 and delete it from the table1

[donsi]

I have two identical tables, TblReturn & TblReturnVoids. On the form there is a Void button. I would like to move selected recorded from tableTblReturn to TblReturnVoids when user clicks on Void button.

I know, Void (YES/NO) is much better option but compliance wants voided records in a separate table. I made append query but don't know how to apply Where Clause to only move selected record from the form.

I followed the post in the forum (see below) and used to code, but it is giving me an error message. How can this be done?

http://www.access-programmers.co.uk/forums/showthread.php?t=183481

Private Sub Command35_Click()
Dim sAppendSql As String
Dim sDeleteSql As String
 
sAppendSql = "INSERT INTO TblReturnVoids.* " & _
                   "SELECT TblReturn.* FROM TblReturn " & _
                   "WHERE TblReturn.ID = " & _
                    Me.ID
 
'sDeleteSql = "DELETE TblPersonData.* " & _
'                  "WHERE tblPersonData.YourPrimaryKey =" & _
'                    Me.primarykeyfield
 
CurrentDb.Execute sInsertSql
'CurrentDb.Execute sDeleteSql

 

[CJ_London]

your delete query does not have a from, should be something like

DELETE * FROM tblPersonData " & _
' "WHERE tblPersonData.YourPrimaryKey =" & _
' Me.primarykeyfield

 

[Minty]

I'd make the compliance dept. create the complicated queries or reports you'll need when they want to see a list of void and non-void transactions in one report...

Do you get to tell them how to organise their jobs

 

[gemma-the-husky]

I do something similar, but I make sure the transfer completed before deleting the records. I also save a record of the proposed deletions.

so.

1. export back up copy of proposed deletions
2. count items in receiving table
3. count items to transfer
4. insert items to transfer
5. count items in receiving table again
6. ensure 5-2=3
7. report error if it doesn't
8. delete items from original table

 

[Galaxiom]

Tell Compliance to stick to doing their job and leave data design to database designers. Using the table to indicate the status of a record is just plain wrong, no exceptions.

However, if they insist on their ignorance, enclose the entire process in a database transaction so the result is either both changes or none.