Saving a global variable to a table as a part of SQL statement using Currenddb.execute

[Anacronos]

I was requested to save each record that has been eliminated. So in the main form I put a button which runs a SQL that copies the data to a table "Eliminated" and then proceeds to actually eliminated from that form but in this new table I need to save the time(Now()) (Field, Default value = Now()... no problem) in which it was eliminated and the name of the user I already created a logging form which saves the name of the user as string in a variable located in a module (public instead of dim) . The deal is this; I can display the name in unbound textboxes no problem. But I´m not sure how to save it along with the other fields that make up the record in the new table.


This is the code used in the button

Dim sql As String
sql = "INSERT INTO Eliminados (Id, Id_dia, Nombre, Edad, Sexo, Total, Anticipo, Resta, Dirección, Telefono, Creado, Contacto, Fecha, Cont) " _
& "SELECT DatosPaciente.Id, DatosPaciente.Id_dia, DatosPaciente.Nombre, DatosPaciente.Edad, DatosPaciente.Sexo, DatosPaciente.Total, DatosPaciente.Anticipo, DatosPaciente.Resta, DatosPaciente.Dirección, DatosPaciente.Telefono, DatosPaciente.Creado, DatosPaciente.Contacto, DatosPaciente.Fecha, DatosPaciente.Cont " _
& "FROM DatosPaciente " _
& "Where Id = '" & Me.txt_id & " ' "


CurrentDb.Execute (sql)
' Docmd.SendObject
DoCmd.RunCommand acCmdDeleteRecord


Any Help will be appreciated, Thank you in advance.

[pbaldy]

Add the field to the INSERT line at the end, and concatenate the value at the end of the SELECT string, with the same delimiters you used for the ID.

[Anacronos]

Excellent , It worked . Thank you

[pbaldy]

Happy to help! By the way, normally you wouldn't delete records, you'd set a status field of some sort to flag them as deleted.