Copy at specific record...

[david]

Hi,

I've been reading tons of posts on how to copy records, but to no
avail....i'm still stuck.

There are three tables: Main, Sub-Form1 & Sub-Form2

I have a form which displays some data.
The PK (ProductID) for each record on the form is an AUTONUMBER.
This form also has a sub-form, linked via ProductID. The subform also
has a PK for each record (subformID) which is an AUTONUMBER.
The form also has a button which links to another for via ProductID,
and which also has it's own AUTONUMBER PK.

So, I need to be able to select a record from the same table that feeds
data to the Main form, as I am trying to let users copy existing
products, and to then just make slight changes to various elements.

Firstly, I need to have a combo box, which would let me select the
product to copy. Then I would have to coy this product to a new
ProductID. Then somehow copy the subform data linked to that selected
product, whilst setting the new linked ProductID.

Confused..I am..

I'm sure there is a work around, but i've never done this
Appreciate all your help

David, uk

[pietlinden]

You use append queries to do it. The catch is that you have to capture
the newly inserted Main table's primary key, and then you can
substitute that value on your future inserts.

You could use DMAX to get the last AutoNumber, but then you'll probably
want to wrap the whole thing in a transaction so you don't get the
wrong PK value because someone just slipped a record in just after you
did.

[John Welch]

David, here's the general idea of how I'd do this, leaving out the actual
code for now.

1) using vb, create a recordset that is set to the data behind the main form
2) with vb, find the record you want to copy (using the PK from the combo
box)
3) insert a new record into this table, using the values from the record
above, with maybe "copy of Product Name" as your product name.
4) use vb to read the Product ID of the newly inserted record and store it
as a variable (be careful if there are multiple users)
5) set the main form to point to the newly created record, close this
recordset and create another based on the subform data table
6) in this new recordset, find each record that has the original PK from the
combo box and get it's field values, then insert a new record with these
values into the recordset (like step 3), but replacing the origingal Product
ID value with the new one you got in step 4
7) close this recordset and requery the subform

I hope this isn't too confusing and gives you a starting point for further
research.
-John
<da***@scene-double.co.uk> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Hi,

I've been reading tons of posts on how to copy records, but to no
avail....i'm still stuck.

There are three tables: Main, Sub-Form1 & Sub-Form2

I have a form which displays some data.
The PK (ProductID) for each record on the form is an AUTONUMBER.
This form also has a sub-form, linked via ProductID. The subform also
has a PK for each record (subformID) which is an AUTONUMBER.
The form also has a button which links to another for via ProductID,
and which also has it's own AUTONUMBER PK.

So, I need to be able to select a record from the same table that feeds
data to the Main form, as I am trying to let users copy existing
products, and to then just make slight changes to various elements.

Firstly, I need to have a combo box, which would let me select the
product to copy. Then I would have to coy this product to a new
ProductID. Then somehow copy the subform data linked to that selected
product, whilst setting the new linked ProductID.

Confused..I am..

I'm sure there is a work around, but i've never done this
Appreciate all your help

David, uk

[david]

Hello John,

Many thanks for your post.
I'm not sure how to do this in VB.
First I created a basic new record in the Products table to get a new
ProductID.
I then tried an append query to add the fields from the products table
from a specific product already listed to my new ID. I just got key
violation errors with anything I tried. I also read that you could not
append data that contains 'Null'. Nearly all my products have some
fields that contain Null entries ?

Appreciate your help again
Thanks so much

David :-)
John Welch wrote:

David, here's the general idea of how I'd do this, leaving out the actual
code for now.

1) using vb, create a recordset that is set to the data behind the main form
2) with vb, find the record you want to copy (using the PK from the combo
box)
3) insert a new record into this table, using the values from the record
above, with maybe "copy of Product Name" as your product name.
4) use vb to read the Product ID of the newly inserted record and store it
as a variable (be careful if there are multiple users)
5) set the main form to point to the newly created record, close this
recordset and create another based on the subform data table
6) in this new recordset, find each record that has the original PK from the
combo box and get it's field values, then insert a new record with these
values into the recordset (like step 3), but replacing the origingal Product
ID value with the new one you got in step 4
7) close this recordset and requery the subform

I hope this isn't too confusing and gives you a starting point for further
research.
-John