Update recordset without affecting table records

[George]

Hi to all,
I have created a recordset using a parametrised SELECT query. Now I would
like to change programmatically the values of the records in one field only
and then add the updated records back to the original table using a command
object with an INSERT statement. My problem is that I don't know how to
detach the recordset from the table that the query is based, so that when I
update the records of the recordset the values of the corresponding records
in my table won't change. I want to have the original records in my table as
well as the updated ones. What's the best way to do this? Thanks in advance,
George.

 

[tina]

use variables to hold the value of the fields in the query record, and then
AddNew to add the values as a new record. in the following example, i used
this test table:

Table2
a (Autonumber, primary key)
b (Text)
c (Text)


Dim rst As DAO.Recordset, strTbl As String
Dim strB As String

strTbl = "SELECT b, c FROM Table2 WHERE a = " _
& InputBox("Enter the value of a, here.")

Set rst = CurrentDb.OpenRecordset(strTbl, dbOpenDynaset)

rst.MoveLast
strB = rst("b")
rst.AddNew
rst("b") = strB
rst("c") = 7
rst.Update

rst.Close
Set rst = Nothing

if that doesn't fit your scenario exactly, then hopefully it will give you
an idea of what direction you can take.

hth