No current record error..loop within loop

[Dale]

HI all, could someone point out how to get rid of the "no current record"
error in this code. What I'm doing is copying a block of records from
"testpatients" into table "All_Forms_Test" so that the block of 10 records
in "testpatients" is repeated for every 10 rows in table "All_Forms_Test"
(1550 records). I can populate the table the way I want, I just can't
figure how to stop the no current record message...tried all manner of
things...thanks again.

Sub FillTestOrders()
Dim count As Long
Dim db As Database
Dim rstPatients As Recordset
Dim rstOrders As Recordset
Set db = CurrentDb
Set rstPatients = db.OpenRecordset("Select * from tblTestPatients where id
between 45 and 70")
Set rstOrders = db.OpenRecordset("All_Forms_Test")

rstPatients.MoveFirst
rstOrders.MoveFirst
Do Until rstOrders.EOF <--- also tried EOF-1, Do Until Not EOF....
Do Until rstPatients.EOF

With rstOrders
.Edit
!Tpatient = rstPatients!TestPatient
.Update
.MoveNext
End With

rstPatients.MoveNext

Loop
rstPatients.MoveFirst

Loop
Set rstPatients = Nothing
Set rstOrders = Nothing
End Sub

 

[John Spencer]

It looks to me as if your inner and outer loops may be flipped.

I'm not quite sure what you are trying to do. You say you want to copy a block
of records from one table to another. This doesn't create any new records, it
simply updates existing records in rstOrders and it does all the records in
rstOrders once with the value in rstPatients and then fails because there is no
current record.

It sounds as if you want to generate some test data by duplicating records (ten
times) from one table into another table. That could be done simply with a
query and the addition of a table with ten rows in it. Simplest would be a
table (tableNumbers) with 10 records with the values 1 to 10 in the only field

INSERT Into All_Forms_Test (TPatient)
SELECT TestPatient FROM tblTestPatients, TableNumbers
WHERE tblTestPatients.ID Between 45 and 70 And TableNumbers.CountColumn <= 10"



Sub FillTestOrders()
Dim count As Long
Dim db As Database
Dim rstPatients As Recordset
Dim rstOrders As Recordset
Set db = CurrentDb
Set rstPatients = db.OpenRecordset("Select * from tblTestPatients where id
between 45 and 70")
Set rstOrders = db.OpenRecordset("All_Forms_Test")

rstPatients.MoveFirst
rstOrders.MoveFirst
Do Until rstOrders.EOF
Do Until rstPatients.EOF

With rstOrders
.Edit
!Tpatient = rstPatients!TestPatient
.Update
.MoveNext
End With
'at this point you have moved beyond EOF for rstOrders,
'but you are still in the inner loop and
'the next rstPatients record will find the
'rstOrders with no current record.

rstPatients.MoveNext

Loop
rstPatients.MoveFirst

Loop
Set rstPatients = Nothing
Set rstOrders = Nothing
End Sub