ID is a primary key. This SQL makes a key violation. There is also the problem of using * in the SELECT but also trying to specify certain fields such as ID. Maybe I'll just have to list every field, and remember to add to the list if new fields are added.

I'm using Access 97 now but the one user who needs this is using Access 2002. It might be possible to use ADO rather than DAO, if it helps.

>It might be easier to change the order slightly:
>1. INSERT INTO mytable (SELECT * FROM NyTable Where ID=CurrentID)
>2. Change values in your final table [mytable].
>
>Please check syntax. This is just an idea.
>
>
>
>>I'm trying to find the easiest and most efficient way to duplicate a record in a recordset, change the value of a couple of fields, and append the new record. The table has a couple dozen fields. I can think of two ways, in general.
>>
>>One is to take the key value and SELECT * FROM mytable WHERE ID = currentID. Then I change a few values in the new recordset of one record, somehow. Then I INSERT INTO mytable(*) SELECT * FROM newrecordset. However, I don't know if I can actually say INSERT INTO mytable(*) rather than having to list all the fields where I put the asterisk.
>>
>>The other way would be to use recordset.GetRows(1), hope that the .GetRows method would start from the current record and not the first record (the help doesn't say) and then .AddNew and change each field one at a time.
>>
>>Doesn anyone know the BEST way?