How to transfer the current field to an another table

[marcihgd]

I need help on this. Mu idea is when the user clicks on the save button, it will transfer its current record to another table and delete it from the existing one.
The SQL insert command works well on its own, but when I added the SQL delete command below it, a syntax error occurs and it doesn't save the record to the other table. What's wrong with this code?


Private Sub command_save_Click()

On Error GoTo Err_command_save_Click

DoCmd.RunSQL "Insert Into [Evaluation_Archive] Select * from [Evaluation] where [Evaluation].[Registration_ID]= " & Me![Registration_ID] & ""

DoCmd.RunSQL "DELETE FROM [Evaluation] Select * from [Evaluation] where [Evaluation].[Registration_ID]= " & Me![Registration_ID] & ""

DoCmd.Close

Exit_command_save_Click:
Exit Sub

Err_command_save_Click:
MsgBox Err.Description
Resume Exit_command_save_Click

End Sub

 

[MajP]

I would probably put this in a transaction. You want to make sure both work
https://codekabinett.com/rdumps.php?Lang=2&targetDoc=how-to-access-transaction

 

[The_Doc_Man]

"DELETE FROM [Evaluation] [COLOR="Red"]Select * from [Evaluation][/COLOR] where [Evaluation].[Registration_ID]= " & Me![Registration_ID] & ""

What is wrong with this code is that the red part doesn't belong there. If you just remove that part, you should do fine.

What trapped you was that the INSERT INTO ... SELECT syntax allows you to copy data out of a table and put it into another table. But there is no DELETE FROM ... SELECT syntax because DELETE works directly, not through a secondary or sub-query.

 

[marcihgd]

thank you very much The_Doc_Man. The code worked as you said.

 

[marcihgd]

I need help again. I've inputted already lots of records and the command works. However, Now the record transfers blank records to [Evaluation_Archive] and is deleted to [Evaluation] table. I'm bemused about it.

 

[Galaxiom]

You are effectively using the location of the record in one table or another as data. This is not proper data structuring. Instead you should leave the records in the table and flag them in some way as completed, archived or whatever you are trying to indicate by moving between tables.

There is rarely a need to archive anyway. Access is perfectly capable of handling millions of records in a table if the indexing and query structures are correctly implemented.

If archiving does need to be done it should be an admin process and fully monitored and verified. Having it randomly done by users on a day to day basis is a disaster waiting to happen.

I would strongly advise you to reconsider what you are attempting to do.

 

[Cronk]

I also would not have an archive table. Just and an extra Yes/no field "Archived". There is no extra space involved if than if the Archive table was included in the main data db (as it appears to be) and it's easy to restore the record if need be. Also the deletion will give rise to faster bloat.

 

[The_Doc_Man]

Galaxiom and Cronk are quite correct. I answered your direct question but they are looking at the overall process, which perhaps I should have described as well. Though I have gone both ways on this problem.

When you have hundreds to maybe a few thousand records in your table, it might be easier to just add one more field called "OBSOLETE" or a similar name, whatever tickles your fancy. Then just set the records to be obsolete at the appropriate time. When you are working on active records, you can simply include a WHERE clause that includes

" AND ( OBSOLETE = FALSE ) "

Doing it this way means you manipulate your tables less often. The INSERT and DELETE queries taken together lead to database bloat more rapidly, which means more physical maintenance actions down the road.

However, this flag method STILL works in your favor since it makes it easier for you to do the archiving step if that is REALLY necessary. You would just have the above WHERE clause element in the INSERT INTO ... SELECT query AND in the DELETE FROM query. You would have very fast selection in this case because the only thing that would be in the WHERE clause would be testing that one flag. SQL can do THAT kind of testing in its sleep.

 

[marcihgd]

Thank you The_Doc_Man, Crank, and Galaxiom. I'll do what you suggest.