copy records within the same table

[markjkubicki]

my goal is to filter a table and then make a copy of all of those records (into that same table) with a change to 1 field (only).
that is to say, if the table had 5 records (a, a, b, c, d), i want to filter for 'a', and then copy those 2 records to 'e' (always a new designation, never b, c, or d..) so that the resulting set of records would be: a, a, b, c, d, e, e) and all of the information in the 'a' and 'e' records would be the same except for the identifying field ('a' or 'e')...

i've got this code (which isn't working) and suspect that i'm going about it with the completely wrong aproach...
any thoughts would be greatly appreciated in advance)

--------------------------------------
Set Db = CurrentDb()
Set rstSourceCuts = Db.OpenRecordset("SELECT * " & _
"FROM tbeAdditionalPages " & _
"WHERE tbeAdditionalPages.type = '" & vSourceType & "';")
Set rstTargetCuts = Db.OpenRecordset("SELECT * " & _
"FROM tbeAdditionalPages " & "';")

If rstSourceCuts.RecordCount > 0 Then
rstSourceCuts.MoveLast

Do Until rstSourceCuts.BOF
rstTargetCuts.AddNew
rstTargetCuts.Type = vType 'vType is the new value, determined previously in the sub, for the field 'type', which is the field whose value does change; all the other field values remain the same.

For Each fld In rstTargetCuts.Fields
strFieldName = fld.Name



If fld.Name <> "Type" Then
rstTargetCuts.Fields(strFieldName) = fld.Value
rstTargetCuts.Update
End If

Next fld

rstSourceCuts.MovePrevious
Loop

End If

[June7]

Why isn't it working - error message, wrong results, no results? Have you step debugged? You can probably do this with a INSERT INTO SELECT sql action and would not need the recordset. Check this for starters http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

[markjkubicki]

"insert into".... elegantly simple
(as a good friend of mine, and many of you in AF.net, have said: "check the obvious first !" )

thanks