Need help copying/pasting a recordset

[Slez via AccessMonster.com]

I am about to start writing code for a command button that will enable users
to copy a recordset, but need a little guidance.

What I'm looking to copy is all related records WHERE ProjectID and BidNumber
are specified in controls on a form, to another recordset where a different
BidNumber is selected. For example: Project ID = 222 and BidNumber = 3, and
I want all related records of BidNumber 3 to be pasted into BidNumber 7 (to
be determined by user).

I'm confident that I'll be able to write the proper code for the SELECT, FROM,
& WHERE phrases, but I have 2 main questions:

1. The code I have experience with in an import application uses the phrase
"INSERT INTO". Do I need to use "PASTE INTO" or something similar for
copying recordsets within the same database?

2. How do I reference the destination record within the code? In the
example above, the new BidNumber would be 7. One other side note to this.
My intent is to be able to add records, not overwrite any existing records
affiliated with, in this case, BidNumber 7.

I appreciate any feedback!
Slez

 

[Douglas J. Steele]

There's no such thing as copying and pasting recordsets. Recordsets are
transient things: they're views of the data contained within the tables.

 

[Bob Hairgrove]

I am about to start writing code for a command button that will enable users
to copy a recordset, but need a little guidance.

What I'm looking to copy is all related records WHERE ProjectID and BidNumber
are specified in controls on a form, to another recordset where a different
BidNumber is selected. For example: Project ID = 222 and BidNumber =3, and
I want all related records of BidNumber 3 to be pasted into BidNumber 7 (to
be determined by user).

I'm confident that I'll be able to write the proper code for the SELECT,FROM,
& WHERE phrases, but I have 2 main questions:

1. The code I have experience with in an import application uses the phrase
"INSERT INTO". Do I need to use "PASTE INTO" or something similar for
copying recordsets within the same database?

Most certainly not! "INSERT INTO" is standard SQL syntax for appending
a row to a table.

2. How do I reference the destination record within the code? In the
example above, the new BidNumber would be 7. One other side note to this.
My intent is to be able to add records, not overwrite any existing records
affiliated with, in this case, BidNumber 7.

I appreciate any feedback!
Slez

The question is whether or not this is a wise idea, i.e. duplicating
so much data. Can you give us some idea of your schema design
(preferably with some DDL so that we don't have to guess about what
are the primary keys and what foreign key constraints might exist)?

 

[Slez via AccessMonster.com]

Perhaps my choice of words "copying and pasting" isn't correct terminoligy,
but ultimately I'm "reproducing" a recordset of one BidNumber into another
BidNumber.

This is within an estimating database, and we are often asked to "re-bid"
projects with a few minor changes. As it turns out, it is much faster to
make a copy of an existing BidNumber's related records and place it under
another BidNumber, as opposed to starting from scratch. I can't have users
simply change the records in the original bid because it is used for
statistical information.

The question of "so much data" is really a non-issue, because I frequently
export these recordsets into another database without fail. The difference
here is that it is remaining in the same table, but into a different
BidNumber.

My tables related as follows:
tblProject / Primary Key is ProjectID / On one side of one-to-many
relationship with...
tblBid / Primary Key is ProjectID & BidNumber / On one side of relationship
with...
tblItem / Primary Key is ProjectID & BidNumber & RoomNumber & ItemNumber / On
one side of relationship with...
tblItemDetail

So in the end, for the duplicate data, ProjectID remains the same, but all
the records in tblItem & tblItemDetail need to be reproduced under a
different BidNumber in tblBid.

Am I correct in understanding that "INSERT INTO" will add records and not
overwrite existing records?

How do I reference the new destination BidNumber?

Thanks
Slez

I am about to start writing code for a command button that will enable users
to copy a recordset, but need a little guidance.

[quoted text clipped - 11 lines]

"INSERT INTO". Do I need to use "PASTE INTO" or something similar for
copying recordsets within the same database?

Most certainly not! "INSERT INTO" is standard SQL syntax for appending
a row to a table.

2. How do I reference the destination record within the code? In the
example above, the new BidNumber would be 7. One other side note to this.

[quoted text clipped - 3 lines]

I appreciate any feedback!
Slez

The question is whether or not this is a wise idea, i.e. duplicating
so much data. Can you give us some idea of your schema design
(preferably with some DDL so that we don't have to guess about what
are the primary keys and what foreign key constraints might exist)?