Hi,

I'm having trouble copying table data to new records.
I have two tables as follows:

*** Specifications (Table)
specification_ID (field) LINKED
product_ID (field)
specification_header (field)

________________________________________

*** Specification_detail (Table)
specification_detail_ID (field)
specification_detail_text (field)
specification_ID (field) LINKED

specification_ID in this table is linked to specification_ID in
Specifications.

________________________________________

On a form, related by product_ID, 'Specifications' fills a subform, no
problem.
When you click a record in this subform, the related records in subform
2 show, which is populated by Specification_detail.

The problem I have is, when a new product is created, and a user
requests to copy a current product, everything is easy to copy as there
is a product_ID involved.
When the data from Specifications is copied to the new product, each
specification is given a new 'specification_ID'.

Data from Specifications is copied via this code:

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

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

Now the table Specifications holds the copied records but with new IDs
for the newly created product.

How do I now copy the data from 'Specification_detail' to the new
product ?
The specification_detail_ID is created automatically, so this is ok,
but 'specification_detail_text' needs to be copied from the current
selected product on the form  and inserted along with the newly created
'specification_ID' (see sql above)??

This is very difficult to get my head around.
I would appreciate any help you can offer.
Do I need to run a loop within a loop

Thanks in advance

David