Adding record failure using nested loops

[pubdude2003 via AccessMonster.com]

hey all,

I have some code that is intended to dupe a set of records in a table and
related table.

It works fine for the first table duping as many records as appear in the
original set but when it comes to duping the records in the related table it
only writes the first related record and then stops, ignoring any additional
Autonumber related records.

My code;

-----------------------------
lngID = ![Job / Estimate Number]

If Me.[Prices].Form.RecordsetClone.RecordCount > 0 Then
Dim db As DAO.Database
Dim PricesRs(1 To 3) As DAO.Recordset
Dim CalcsRs(1 To 3) As DAO.Recordset
Dim PricesID As Integer
Dim ix As Single

ix = 1

Set db = CurrentDb
Set PricesRs(1) = db.OpenRecordset("SELECT * FROM [1 Printing Price] WHERE
[Job / Estimate Number] = " & Me.[Job / Estimate Number])
Set PricesRs(2) = PricesRs(1).Clone
Set PricesRs(3) = PricesRs(1).Clone

Do Until ix > (Me.[Prices].Form.RecordsetClone.RecordCount)
PricesRs(3).AddNew
PricesID = PricesRs(3)![Autonumber]
PricesRs(3)![Job / Estimate Number] = lngID
PricesRs(3)![Q 1] = PricesRs(2)![Q 1]
PricesRs(3)![Q 1 Header] = PricesRs(2)![Q 1 Header]

PricesRs(3).Update 'Must do update here as the Calcs table requires the
record to be saved.


'Create the associated CALCS record
Set CalcsRs(1) = db.OpenRecordset("SELECT * FROM [Calcs] WHERE [Autonumber] =
" & PricesRs(1)!(Autonumber])
Set CalcsRs(2) = CalcsRs(1).Clone
Set CalcsRs(3) = CalcsRs(1).Clone

Do Until CalcsRs(1).EOF
CalcsRs(3).AddNew
CalcsRs(3)![Estimate Number] = lngID
CalcsRs(3)![Autonumber] = PricesID
CalcsRs(3)![Ref] = CalcsRs(2)![Ref]
CalcsRs(3)![1] = CalcsRs(2)![1]
CalcsRs(3)![hiddensym1] = CalcsRs(2)![hiddensym1]
CalcsRs(3)![descript1] = CalcsRs(2)![descript1]
CalcsRs(3)![2] = CalcsRs(2)![2]
CalcsRs(3)![hiddensym2] = CalcsRs(2)![hiddensym2]
CalcsRs(3)![descript2] = CalcsRs(2)![descript2]

CalcsRs(3).Update
CalcsRs(1).MoveNext

PricesRs(1).MoveNext
PricesRs(2).MoveNext
Exit Do
Loop
ix = ix + 1
Loop

 

[pubdude2003 via AccessMonster.com]

sorry, posted to wrong group