Crear una consulta...

PARAMETERS pId Long, pIdNuevo Long;
INSERT INTO LaTabla ( Id, Campo1, Campo2 )
SELECT pIdNuevo AS NuevoId, Campo1, Campo2
FROM LaTabla
WHERE Id=pId;

Sub DuplicarRegistro(Id As Long, NuevoId As Long)
    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.QueryDefs("DuplicarRegistro")
    qdf("pId") = Id
    qdf("pIdNuevo") = NuevoId
    qdf.Execute dbFailOnError
End Sub