Delete Syntax Issue

[usfarang]

I'm getting an error trying to run this code. See attached screenshot & code.


Thanks,

Dim strMsg As String Dim strCopy, strSQL As String
Dim RST As Recordset




DoCmd.RunSQL "DELETE * from tblEmployees WHERE EmpID=" & Me.EmpID
strCopy = "INSERT Into tblDeletedEmployees SELECT tblEmployees. * from tblEmployees WHERE (tblEmployees.EmpID=" & Me.EmpID & ")"
strSQL = "delete * from tblEmployees where EmpID = " & Me.EmpID
DoCmd.RunSQL strCopy
DoCmd.RunSQL strSQL

[June7]

Why the same DELETE twice?

There is a space between tblEmployees. and * in the INSERT sql. Don't need table prefix.

strCopy = "INSERT Into tblDeletedEmployees SELECT * from tblEmployees WHERE (tblEmployees.EmpID=" & Me.EmpID & ")"

[usfarang]

Still getting an error. This is my corrected code.

strCopy = "INSERT Into tblDeletedEmployees SELECT * from tblEmployees WHERE tblEmployees.EmpID=" & Me.EmpID & ""

[June7]

Syntax looks correct.

Is there an autonumber field in tblDeletedEmployees.

Why do you even have a tblDeletedEmployees? Why not just flag records as "Inactive"?

[usfarang]

I mark records as deleted in tblAuditTrail and also need a copy in tblDeletedEmployees because of the details and for reports.
No, there is no PK in the table but there is a number key not set to duplicates or anything.

[usfarang]

Ok, I simplified it and took your advice and set it to Inactive.
Thanks,

[Welshgasman]

I mark records as deleted in tblAuditTrail and also need a copy in tblDeletedEmployees because of the details and for reports.
No, there is no PK in the table but there is a number key not set to duplicates or anything.

Well that is telling you EmpID is empty?