How do I Do Until end of recordset when looping through records?

For the life of me I can't seem to figure out how to set up this loop to stop when it hits the end of the recordset?

I thought Do Until rs.EOF was going to do the trick but it blows right through that and then errors out with code 3021 No Current Record.

Thanks for the help!
LVL 2
Jarred MeyerProduction ManagerAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
test this revision

    rs.MoveLast
    rs.MoveFirst
    Do Until rs.EOF
        pn = rs!odPartNum
        rs.Edit
        rs!RTOnHandQty = rs!TotalQtyWIPINV
        rs.Update
        cqty = 0
        cRecord = 0

            
        Do
            
            If rs!odPartNum <> pn then exit do
                  If cRecord = 0 Then
                rs.Edit
                rs!RTOnHandQty = rs!RTOnHandQty - rs!RemReqQty
                rs.Update
            Else
                rs.Edit
                rs!RTOnHandQty = cqty + rs!InvWIPQty - rs!RemReqQty
                rs.Update
            End If
           
            If rs!RTOnHandQty < 0 Then
                rs.Edit
                rs!UnderQty = "Yes"
                rs.Update
            Else
                rs.Edit
                rs!UnderQty = "No"
                rs.Update
            End If
            cRecord = cRecord + 1
            cqty = rs!RTOnHandQty
            rs.MoveNext
            
      if rs.EOF then exit do

        Loop
    Loop
    rs.Close
    Set rs = Nothing
    rs1.Close
    Set rs1 = Nothing
0
 
Rey Obrero (Capricorn1)Commented:
here is the format
 Do Until rs.EOF


rs.movenext
Loop

Open in new window


what do you have that gives you the error?  post it here
0
 
Saurabh Singh TeotiaCommented:
One quick addition to rey comment..just a fail proof method you move the cursor to the first line and then start the loop which is:-

rs.movefirst
Do Until rs.EOF


rs.movenext
Loop

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you're going to MoveFirst, you should first MoveLast to fully populate:

rst.MoveLast
rst.MoveFirst

However, that method has not been needed for quite some time. At one point, there were issues with populating DAO recordsets fully, and failing to call MoveLast and MoveFirst could result in missing records. I don't recall that being an issue since 2003 ...
0
 
Jarred MeyerProduction ManagerAuthor Commented:
rs.MoveFirst
    Do Until rs.EOF
        pn = rs!odPartNum
        rs.Edit
        rs!RTOnHandQty = rs!TotalQtyWIPINV
        rs.Update
        cqty = 0
        Do Until rs!odPartNum <> pn Or rs.EOF
            If cqty = 0 Then
                rs.Edit
                rs!RTOnHandQty = rs!RTOnHandQty - rs!RemReqQty
                rs.Update
            Else
                rs.Edit
                rs!RTOnHandQty = cqty - rs!RemReqQty
                rs.Update
            End If
            If rs!RTOnHandQty < 0 Then
                rs.Edit
                rs!UnderQty = "Yes"
                rs.Update
            Else
                rs.Edit
                rs!UnderQty = "No"
                rs.Update
            End If
            
            cqty = rs!RTOnHandQty
            rs.MoveNext
        Loop
    Loop

Open in new window


rs.eof is testing true but it continues the code instead of jumping out of the loop
0
 
Saurabh Singh TeotiaCommented:
For the first loop which you start you don't have rs.movenext after row-30 and that's the reason it's stuck in continuous loop...
0
 
Rey Obrero (Capricorn1)Commented:
aside from missing a rs.movenext after row 30
looks like you need two recordsets to accomplish what you are trying to do..


can you (in plain english) explain in detail what you are trying to accomplish?
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Ok, It doesn't get stuck in the loop. The Second loop (inside the first) moves it to the next RS record but kicks back out to the outer loop when it see's it's on a new part number. So the inner loop is pusshing it forward which work exactly like I want it to.

Here is the rest of the working code (up until it runs out of records and throws 3021: No Current Record at line        Do Until rs!odPartNum <> pn Or rs.EOF


    
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("tblTEMPMRP")
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM tblTEMPMRP"
    DoCmd.RunSQL "DELETE * FROM tblTEMPCompWIPQty"
    DoCmd.OpenQuery "qryInventoryShipments"
    DoCmd.OpenQuery "qryAPDCompWIPQty"
    DoCmd.SetWarnings True
    rs.MoveFirst
    rqty = 0
    
    Do Until rs.EOF
        Debug.Print rs!odPartNum
        Set rs1 = CurrentDb.OpenRecordset("SELECT tblTEMPCompWIPQty.jhPartNum, tblTEMPCompWIPQty.InvWIPQty, IssuedQty " & _
                                            "FROM tblTEMPCompWIPQty " & _
                                            "WHERE tblTEMPCompWIPQty.jhDueDate<= #" & rs!orReqDate & "#" & _
                                            "AND tblTEMPCompWIPQty.jhPartNum= '" & rs!odPartNum & "' AND IssuedQty = False")
        If rs1.BOF And rs1.EOF Then
            rs.Edit
            rs!InvWIPQty = 0
            rs!TotalQtyWIPINV = rs!InvWIPQty + rs!OnHandQty
            rs.Update
        Else
            rs1.MoveFirst
            iwq = 0
            Do Until rs1.EOF
                If iwq = 0 Then
                    rs.Edit
                    rs!InvWIPQty = rs1!InvWIPQty
                    rs!TotalQtyWIPINV = rs!InvWIPQty + rs!OnHandQty
                    rs.Update
                Else
                    rs.Edit
                    rs!InvWIPQty = rs1!InvWIPQty + iwq
                    rs!TotalQtyWIPINV = rs!InvWIPQty + rs!OnHandQty
                    rs.Update
                End If
                rs1.Edit
                rs1!IssuedQty = True
                rs1.Update
                iwq = rs!InvWIPQty
                rs1.MoveNext
            Loop
        End If
        rs.MoveNext
    Loop
        
    rs.MoveLast
    rs.MoveFirst
    Do Until rs.EOF
        pn = rs!odPartNum
        rs.Edit
        rs!RTOnHandQty = rs!TotalQtyWIPINV
        rs.Update
        cqty = 0
        Do Until rs!odPartNum <> pn Or rs.EOF
            If cqty = 0 Then
                rs.Edit
                rs!RTOnHandQty = rs!RTOnHandQty - rs!RemReqQty
                rs.Update
            Else
                rs.Edit
                rs!RTOnHandQty = cqty - rs!RemReqQty + rs!InvWIPQty
                rs.Update
            End If
            If rs!RTOnHandQty < 0 Then
                rs.Edit
                rs!UnderQty = "Yes"
                rs.Update
            Else
                rs.Edit
                rs!UnderQty = "No"
                rs.Update
            End If
            
            cqty = rs!RTOnHandQty
            rs.MoveNext
        Loop
    Loop

Open in new window

0
 
Gustav BrockCIOCommented:
This doesn't make sense:

    Set rs = CurrentDb.OpenRecordset("tblTEMPMRP")
    DoCmd.RunSQL "DELETE * FROM tblTEMPMRP"

First you open the recordset, then you delete it in another process. The one hand doesn't know what the other does.

You will have to reorganize so you don't manipulate the tables behind the scene.

/gustav
0
 
Jarred MeyerProduction ManagerAuthor Commented:
I have to apologize for getting away from this question for so long! I was pulled in a completely different direction for a couple of weeks and hadn't had a chance to get back into this until just now. To catch everything up let me repost the code where I'm having this issue:
Private Sub Command45_Click()
    Dim rs As DAO.Recordset


    Set rs = CurrentDb.OpenRecordset("tblTEMPMRP")
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM tblTEMPMRP"
    DoCmd.RunSQL "DELETE * FROM tblTEMPCompWIPQty"
    DoCmd.OpenQuery "qryInventoryShipments"
    DoCmd.OpenQuery "qryAPDCompWIPQty"
    DoCmd.SetWarnings True
    rs.MoveFirst
    rqty = 0
    
    Do Until rs.EOF
        Debug.Print rs!odPartNum
        Set rs1 = CurrentDb.OpenRecordset("SELECT tblTEMPCompWIPQty.jhPartNum, tblTEMPCompWIPQty.InvWIPQty, IssuedQty " & _
                                            "FROM tblTEMPCompWIPQty " & _
                                            "WHERE tblTEMPCompWIPQty.jhReqDueDate<= #" & rs!orReqDate & "#" & _
                                            "AND tblTEMPCompWIPQty.jhPartNum= '" & rs!odPartNum & "' AND IssuedQty = False")
        If rs1.BOF And rs1.EOF Then
            rs.Edit
            rs!InvWIPQty = 0
            rs!TotalQtyWIPINV = rs!InvWIPQty + rs!OnHandQty
            rs.Update
        Else
            rs1.MoveFirst
            iwq = 0
            Do Until rs1.EOF
                If iwq = 0 Then
                    rs.Edit
                    rs!InvWIPQty = rs1!InvWIPQty
                    rs!TotalQtyWIPINV = rs!InvWIPQty + rs!OnHandQty
                    rs.Update
                Else
                    rs.Edit
                    rs!InvWIPQty = rs1!InvWIPQty + iwq
                    rs!TotalQtyWIPINV = rs!InvWIPQty + rs!OnHandQty
                    rs.Update
                End If
                rs1.Edit
                rs1!IssuedQty = True
                rs1.Update
                iwq = rs!InvWIPQty
                rs1.MoveNext
            Loop
        End If
        rs.MoveNext
    Loop
        
    rs.MoveLast
    rs.MoveFirst
    Do Until rs.EOF
        pn = rs!odPartNum
        rs.Edit
        rs!RTOnHandQty = rs!TotalQtyWIPINV
        rs.Update
        cqty = 0
        cRecord = 0
        Do Until rs!odPartNum <> pn Or rs.EOF
            If cRecord = 0 Then
                rs.Edit
                rs!RTOnHandQty = rs!RTOnHandQty - rs!RemReqQty
                rs.Update
            Else
                rs.Edit
                rs!RTOnHandQty = cqty + rs!InvWIPQty - rs!RemReqQty
                rs.Update
            End If
            
            If rs!RTOnHandQty < 0 Then
                rs.Edit
                rs!UnderQty = "Yes"
                rs.Update
            Else
                rs.Edit
                rs!UnderQty = "No"
                rs.Update
            End If
            cRecord = cRecord + 1
            cqty = rs!RTOnHandQty
            rs.MoveNext
        Loop
    Loop
    rs.Close
    Set rs = Nothing
    rs1.Close
    Set rs1 = Nothing
    
End Sub

Open in new window


I am getting the following error at the very end of the dataset where it runs out of records:
error
Thanks for all of your input and again, I apologize for abandoning this question!

P.S. Gustav, on your last comment, I'm running that delete to clear all the records out of that temp table. It is not actually deleting the temp table. I'm setting it as RS so it can be brought up later in the code.
0
 
Gustav BrockCIOCommented:
OK, but at least you must reverse the two statements:

     DoCmd.RunSQL "DELETE * FROM tblTEMPMRP"
     Set rs = CurrentDb.OpenRecordset("tblTEMPMRP")

/gustav
0
 
Jarred MeyerProduction ManagerAuthor Commented:
OK, but at least you must reverse the two statements:

     DoCmd.RunSQL "DELETE * FROM tblTEMPMRP"
     Set rs = CurrentDb.OpenRecordset("tblTEMPMRP")

/gustav

Ok, I flipped them:
    Dim rs As DAO.Recordset
'   On Error GoTo Command45_Click_Error

    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM tblTEMPMRP"
    DoCmd.RunSQL "DELETE * FROM tblTEMPCompWIPQty"
    DoCmd.OpenQuery "qryInventoryShipments"
    DoCmd.OpenQuery "qryAPDCompWIPQty"
    DoCmd.SetWarnings True
    Set rs = CurrentDb.OpenRecordset("tblTEMPMRP")
    rs.MoveFirst
    rqty = 0
    
    Do Until rs.EOF
        Debug.Print rs!odPartNum
        Set rs1 = CurrentDb.OpenRecordset("SELECT tblTEMPCompWIPQty.jhPartNum, tblTEMPCompWIPQty.InvWIPQty, IssuedQty " & _
                                            "FROM tblTEMPCompWIPQty " & _
                                            "WHERE tblTEMPCompWIPQty.jhReqDueDate<= #" & rs!orReqDate & "#" & _
                                            "AND tblTEMPCompWIPQty.jhPartNum= '" & rs!odPartNum & "' AND IssuedQty = False")
        If rs1.BOF And rs1.EOF Then
            rs.Edit
            rs!InvWIPQty = 0
            rs!TotalQtyWIPINV = rs!InvWIPQty + rs!OnHandQty
            rs.Update
        Else
            rs1.MoveFirst
            iwq = 0
            Do Until rs1.EOF
                If iwq = 0 Then
                    rs.Edit
                    rs!InvWIPQty = rs1!InvWIPQty
                    rs!TotalQtyWIPINV = rs!InvWIPQty + rs!OnHandQty
                    rs.Update
                Else
                    rs.Edit
                    rs!InvWIPQty = rs1!InvWIPQty + iwq
                    rs!TotalQtyWIPINV = rs!InvWIPQty + rs!OnHandQty
                    rs.Update
                End If
                rs1.Edit
                rs1!IssuedQty = True
                rs1.Update
                iwq = rs!InvWIPQty
                rs1.MoveNext
            Loop
        End If
        rs.MoveNext
    Loop
        
    rs.MoveLast
    rs.MoveFirst
    Do Until rs.EOF
    'Do While Not rs.EOF
        pn = rs!odPartNum
        rs.Edit
        rs!RTOnHandQty = rs!TotalQtyWIPINV
        rs.Update
        cqty = 0
        cRecord = 0
        Do Until rs.EOF Or rs!odPartNum <> pn
        'Do While rs!odPartNum = pn Or Not rs.EOF
            If cRecord = 0 Then
                rs.Edit
                rs!RTOnHandQty = rs!RTOnHandQty - rs!RemReqQty
                rs.Update
            Else
                rs.Edit
                rs!RTOnHandQty = cqty + rs!InvWIPQty - rs!RemReqQty
                rs.Update
            End If
            
            If rs!RTOnHandQty < 0 Then
                rs.Edit
                rs!UnderQty = "Yes"
                rs.Update
            Else
                rs.Edit
                rs!UnderQty = "No"
                rs.Update
            End If
            cRecord = cRecord + 1
            cqty = rs!RTOnHandQty
            rs.MoveNext
        Loop
    Loop
    rs.Close
    Set rs = Nothing
    rs1.Close
    Set rs1 = Nothing
    
'   On Error GoTo 0
'   Exit Sub

'Command45_Click_Error:
'    If Err.Number = 3021 Then
'        Me.Requery
'        rs.Close
'        Set rs = Nothing
'        rs1.Close
'        Set rs1 = Nothing
'        Exit Sub
'    Else
'        Call ErrHndlr(Me.Form.Name, Me.Form.ActiveControl.Name, "Line: " & Erl & "; " & Err.Number, Err.Description, "Command45_Click")
'        MsgBox "System Error: " & Err.Description & vbCrLf & "An error has occured. Please contact an Admin."
'        Exit Sub
'    End If
    
End Sub

Open in new window

0
 
Gustav BrockCIOCommented:
> 'Do While rs!odPartNum = pn Or Not rs.EOF

You can't check a field value at EOF where no record exists.

Try something like:

    Do While Not rs.EOF
        If rs!odPartNum = pn Then
           ' run code
        Else
           ' Skip and continue.
        End If

/gustav
0
 
Jarred MeyerProduction ManagerAuthor Commented:
All good answers, I'll also add that I figured out a method using a similar idea to the accepted answers:

    Dim rs As DAO.Recordset
    Dim cRecord As Long
    Dim rCount As Long
    Dim mRecord As Long
   
    DoCmd.OpenForm "frmCurrentPart"
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM tblTEMPMRP"
    DoCmd.RunSQL "DELETE * FROM tblTEMPCompWIPQty"
    DoCmd.OpenQuery "qryInventoryShipments"
    DoCmd.OpenQuery "qryAPDCompWIPQty"
    DoCmd.SetWarnings True
    Set rs = CurrentDb.OpenRecordset("tblTEMPMRP")
    rs.MoveFirst
    rqty = 0
   
    Do Until rs.EOF
        Pause 0.001
        Set rs1 = CurrentDb.OpenRecordset("SELECT tblTEMPCompWIPQty.jhPartNum, tblTEMPCompWIPQty.InvWIPQty, IssuedQty " & _
                                            "FROM tblTEMPCompWIPQty " & _
                                            "WHERE tblTEMPCompWIPQty.jhReqDueDate<= #" & rs!orReqDate & "#" & _
                                            "AND tblTEMPCompWIPQty.jhPartNum= '" & rs!odPartNum & "' AND IssuedQty = False")
        If rs1.BOF And rs1.EOF Then
            rs.Edit
            rs!InvWIPQty = 0
            rs!TotalQtyWIPINV = rs!InvWIPQty + rs!OnHandQty
            rs.Update
        Else
            rs1.MoveFirst
            iwq = 0
            Do Until rs1.EOF
                If iwq = 0 Then
                    rs.Edit
                    rs!InvWIPQty = rs1!InvWIPQty
                    rs!TotalQtyWIPINV = rs!InvWIPQty + rs!OnHandQty
                    rs.Update
                Else
                    rs.Edit
                    rs!InvWIPQty = rs1!InvWIPQty + iwq
                    rs!TotalQtyWIPINV = rs!InvWIPQty + rs!OnHandQty
                    rs.Update
                End If
                rs1.Edit
                rs1!IssuedQty = True
                rs1.Update
                Forms!frmCurrentPart!txtCurrentPart = rs1!jhPartNum
                iwq = rs!InvWIPQty
                rs1.MoveNext
            Loop
        End If
        rs.MoveNext
    Loop
       
    rs.MoveLast
    rCount = rs.RecordCount
    rs.MoveFirst
    Do Until rs.EOF
        pn = rs!odPartNum
        rs.Edit
        rs!RTOnHandQty = rs!TotalQtyWIPINV
        rs.Update
        cqty = 0
        cRecord = 0
        Do Until rs!odPartNum <> pn
            Pause 0.001
            If cRecord = 0 Then
                rs.Edit
                rs!RTOnHandQty = rs!RTOnHandQty - rs!RemReqQty
                rs.Update
            Else
                rs.Edit
                rs!RTOnHandQty = cqty + rs!InvWIPQty - rs!RemReqQty
                rs.Update
            End If
            Pause 0.002
            Forms!frmCurrentPart!txtCurrentPart = rs!odPartNum
            If rs!RTOnHandQty < 0 Then
                rs.Edit
                rs!UnderQty = "Yes"
                rs.Update
            Else
                rs.Edit
                rs!UnderQty = "No"
                rs.Update
            End If
            cRecord = cRecord + 1
            mRecord = mRecord + 1
            cqty = rs!RTOnHandQty
            If mRecord = rCount Then
                rs.Close
                Set rs = Nothing
                rs1.Close
                Set rs1 = Nothing
                Me.Requery
                DoCmd.Close acForm, "frmCurrentPart"
                Exit Sub
            Else
                rs.MoveNext
            End If
        Loop
    Loop
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.