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_d etails.

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

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

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

Specification Table has the following fields:

specification_I D (Autonumber)
product_ID (Number)
specification_h eader (Text)

Specification_d etail Table has the following fields:

specification_d etail_ID (Autonumber)
specification_d etail_text (Text)
specification_I D (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_h eader)
"
MySql4 = MySql4 & "SELECT " & NewProductCode & " as NewProductID,
Specifications. specification_h eader 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_d etails.

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

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

So walk down the relationships - grab the PK of the parent record of
Specification_d etails, 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.