Loop within a loop ???

[David]

Hi,

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

*** Specifications (Table)
specification_I D (field) LINKED
product_ID (field)
specification_h eader (field)

_______________ _______________ __________

*** Specification_d etail (Table)
specification_d etail_ID (field)
specification_d etail_text (field)
specification_I D (field) LINKED

specification_I D in this table is linked to specification_I D 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_d etail.

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_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

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

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_d etail_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

[pietlinden@hotmail.com]

Re: Loop within a loop ???

use the foreign key for the subtable.

SELECT ...
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
records...)