Enumerate thru recordset

[DaveTappenden]

I need to copy a particular record's contents and append it to the end of the table. The copied record will have the same contents except for the new key field (autonum).
The table has over 100 fields, so I used the code below to loop thru each recordset item.
However, I get a runtime error 3219 (invalid operation) at the line marked ****
I have tried .Fields(i).name and !Fields(i).name but neither will work.
Can anyone advise me as to the correct code to use?
Many thanks.


Dim db As DAO.Database
Dim rs As DAO.Recordset, rsNew As DAO.Recordset
Dim fld As DAO.Field
Set db = CurrentDb()

Set rs = db.OpenRecordset("Select * From Table1 where ID = 1", dbOpenSnapshot)
Set rsNew = db.OpenRecordset("Table1", dbOpenDynaset)
With rsNew
.AddNew
For i = 0 To .Fields.Count - 1
If rsNew.Fields(i).name <> &quot;ID&quot; Then ' ignore key
.Fields(i).name = rs.Fields(i) ' **** error 3219 here
End If
Next i
End With

 

[bboffin]

Try:

    .Fields(i).Value = rs.Fields(i).Value

You are trying to change the name of the field which is forbidden in this context.

 

[DaveTappenden]

thanks bboffin, works a treat.