Copy record from recordset

[Secen]

Is there a way I can copy an entire record from one
recordset to another without copying each individual
field? I open a recordset that has 6000+ records and 30
fields and apply a filter, I then want to copy those
filtered records into another recordset that is set to an
empty table that has the exact same structure as the
original. I have tried clone but doesn't seem to work:

While Not rstActivity.EOF
rstTrans.AddNew
Set rstTrans = rstActivity.Clone
rstTrans.Update
rstActivity.MoveNext
Wend

I get an error that says "Update without Addnew or Edit
called".

Since the tables are in the same database so I don't think
I can use PopulatePartial. Any ideas?

 

[Tim Ferguson]

I then want to copy those
filtered records into another recordset that is set to an
empty table

You need to get the original SQL and convert it into an append query:

INSERT INTO EmptyTable(one, two, three)
SELECT Aee, Bee, Cee
FROM TheOldTable
WHERE Something=True

and then db.Execute it.

Remember that recordsets do not actually "exist" in terms of data: they are
only ways of describing a particular set of columns and rows that actually
live in the tables. You can't copy a view from one window to another one,
and you can't copy a recordset either.

HTH


Tim F

 

[Secen]

-----Original Message-----
If rstActivity is the entire recordset you want to copy (instead of a single
record), I think that just one

Set rstTrans = rstActivity.Clone

should do the job. You should be able to instansiate rstTrans just like that
- I don't think you need to bodther with .Addnew or .Update.


.

The clone operation completes successfully - the record is
never saved in the blank table, however.

 

[Secen]

That's got it - thanks for your help.

-----Original Message-----


You need to get the original SQL and convert it into an append query:

INSERT INTO EmptyTable(one, two, three)
SELECT Aee, Bee, Cee
FROM TheOldTable
WHERE Something=True

and then db.Execute it.

Remember that recordsets do not actually "exist" in terms of data: they are
only ways of describing a particular set of columns and rows that actually
live in the tables. You can't copy a view from one window to another one,
and you can't copy a recordset either.

HTH


Tim F

.