Update recordset without affecting table records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Back
Top