Loop within a loop ???


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


On a form, related by product_ID, 'Specifications' fills a subform, no
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


4/12/2006 4:10:07 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

1 Replies

Similar Articles

[PageSpeed] 20

use the foreign key for the subtable.

FROM ChildTable
WHERE ForeignKey=...

and then just turn that into an insert query. (Well, of course, you
have to capture the new PK from teh *new* record and then update those

pietlinden (2926)
4/12/2006 6:01:55 PM