simplest way to duplicate a record from recordset but change just one fields value

[Happy YN]

I need to run a loop which will duplicate an existing record in a table but change just one field using data supplied. I am not fluent in ADO ,I have seen the addnew method but as far as I know one has to specify all the fields and heir values. How can I say copy the records but change fieldx to whatever and run this for each of my new values of that field. The recordset is a sql (bringing up a single record) which looks like "select * from tbl where ID=256" so there is no telling how many fields there are in the table. All I am given is the name of the field where there is to be a change.Perhaps ADO is not the correct method & I should use append sql but I am still unsure how to accomplish what I want
Thanks

 

[Happy YN]

Over 40 views and not one reply?

 

[WayneRyan]

Happy,

Roughly something like this:

Dim i As Long
Dim dbs As DAO.Database
Dim rst1 AS DAO.Recordset
Dim rst2 AS DAO.Recordset

Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("Select * From YourTable Where YourPK = " & Me.YourPK)
If Not rst1.EOF And Not rst1.BOF
   Set rst2 = dbs.OpenRecordset("YourTable")
   rst2.AddNew
   For i = 0 to rst1.Fields.Count - 1
       rst2.Fields(i) = rst1.Fields(i)
       Next i
   rst2!SomeField = "SomeOtherValue"
   rst2.Update
End If

Set rst2 = Nothing
Set rst1 = Nothing
Set dbs = Nothing

hth,
Wayne

 

[Happy YN]

Sounds good! will try & report back. Thanks

 

[Happy YN]

sorry for delay
Yes it worked very well. However had to exclude the automatic numbering ID. Thanks again

 

[modest]

If you're just creating a new table, why not use SQL

SELECT <fields> INTO <new table name> FROM <old table name> WHERE ID=<value>