Do loop that never stops? 1

[ffleitas]

Hello programmers,

I have this code in a subform that is supposed to execute and stop at the end of subform data. This is the code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim j As Integer
Dim stDocName As String

stDocName = "qryPickupSubPlusQnty"

'Do

Set db = CurrentDb
Set rs = db.OpenRecordset("tblPickupsSub", dbOpenDynaset)


'If Forms![frmPickupsOnCall]![frmPickupsOnCallSub]![ContainerID] <> &quot;&quot; Then

If Me!frmPickupsOnCallSub![Quantity] > 1 Then

With rs
.MoveFirst
Do
j = 1
For i = 1 To Me!frmPickupsOnCallSub![Quantity]
.AddNew
.Fields(&quot;PickupID&quot = Me![PickupID]
.Fields(&quot;ContainerID&quot = Me!frmPickupsOnCallSub![ContainerID]
.Fields(&quot;BarCode&quot = Me!frmPickupsOnCallSub![BarCode]
.Fields(&quot;ContainerName&quot = Me!frmPickupsOnCallSub![ContainerName]
.Fields(&quot;ContainerDescription&quot = Me!frmPickupsOnCallSub![ContainerDescription]
.Fields(&quot;Quantity&quot = 1
.Update
Select Case i
Case 4, 8, 12, 16: j = j + 1
End Select
Next i
.MoveNext
Loop Until .EOF
End With

Set rs = Nothing
Set db = Nothing

Else

End If
'End If
'Loop Until Form_frmPickupsOnCallSub!ContainerID.Value = &quot;&quot;


DoCmd.OpenQuery stDocName
End Sub

Where did I go wrong?

 

[Rolliee]

When you add a new rec and then ask if there are any more recs, the answer will always be YES.


Rollie E

 

[jiqjaq]

I believe the reason you never get out of the loop is that you have added more records to the recordset via the .ADD before you check for EOF.

If rs is only used for adding records to the table, you only need the query you have, but you should take out the MoveNext and EOF check because they don't matter - You already know when to stop adding records from the [Quantity].

If the recordset's query is actually used for determining the number of records you're going to add, then you need 2 recordsets, one for the original set of records with dbOpenSnaphot, and a second one for the records to be added.