Hi Gary, thanks for the code which works perfect.

[123rose]

Hi Gary,

In the code "INSERT INTO tblNew
SELECT tblOld.* FROM tblOld WHERE some criteria.", if I just want to append whatever record has been selected into tblNew, how should I write the "Where..." statement.

My whole logic is: I have 2 buttons, one is Search, another is Delete. After finding one record, I want to go ahead and append it to another table and delete it from this table.

Thank you very much for your great help.

Have a great day,

Rose

 

[Darrylles]

Hi Rose,

Better off clicking 'Mark this post as a helpful/expert post!'

Thus saving space on the server.

lol

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[SBendBuckeye]

Dim strSQL As String

strSQL = "INSERT INTO tblNew SELECT tblOld.* FROM tblOld " _
& "WHERE tblOld.SomeFieldName = " & Delete
RunCmd.RunSQL strSQL

A couple things to watch out for - If the matching items are strings then you need single quotes around your criteria "Where SearchField = '" & ControlName & "'". If they are numeric, nothing like the above is needed. If they are dates then replace the single quotes with pound signs.

Good Luck!