Copy related sub records ............

[David]

Hi,

I have an MS Access app which lets me create a new product by copying
data from another selected product.
All works fine, except one of the copy updates which I cannot fathom.

The code creates a new product and copies over the parent data from the
other product first.
Then related data from other tables is copied accross using the new
Product_ID. ok

One of the relate records 'specifications' are copied accross to the
new Product.
The problem is that Specifications also have a related sub table called
Specification-details.

On my app, you select a product and then select the specifications form
from a combo.
On this specifications form, there is the Specification data in a
continuous format, and another sub-form with specification_details.

When you click on a specification, the relevant specification_detail
appears in the sub-form below. This is pretty standard.

The problem I am having is copying the Specification_details to the new
product.

---------------------------------------------------

Specification Table has the following fields:

specification_ID (Autonumber)
product_ID (Number)
specification_header (Text)

Specification_detail Table has the following fields:

specification_detail_ID (Autonumber)
specification_detail_text (Text)
specification_ID (Number) related to Specification table !

--------------------------------------------------------

This code copies the parent 'Specifications' over to the new product
and works great.
(NewProductCode = the new Product_ID)
(currentid = The Product_ID of the product to copy)

MySql4 = "INSERT INTO Specifications (product_ID, specification_header)
"
MySql4 = MySql4 & "SELECT " & NewProductCode & " as NewProductID,
Specifications.specification_header FROM Specifications "
MySql4 = MySql4 & "WHERE Specifications.product_ID = " & currentid
db.Execute MySql4, dbFailOnError

----------------------------------------------------------

How do I copy the related 'Specification_details' over to the new
product as there is no Product_ID in this table. The related field is
'Specification_ID'

Appreciate all your help

Thanks in advance

David Gordon

[pietlinden]

On my app, you select a product and then select the specifications form

from a combo.
On this specifications form, there is the Specification data in a
continuous format, and another sub-form with specification_details.

When you click on a specification, the relevant specification_detail
appears in the sub-form below. This is pretty standard.

The problem I am having is copying the Specification_details to the new
product.

So walk down the relationships - grab the PK of the parent record of
Specification_details, and then use an append query with that PK/FK as
the filter, and then append to the table it should go in. if you're
missing records or something hasn't saved, well then deal with that
problem first - force Access to save the record.