Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: Trouble with Loop in recordset

Results 1 to 3 of 3
Share This Thread →
  1. #1
    meratigoerr's Avatar
    meratigoerr is offline Contributing User
    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58

    Trouble with Loop in recordset

    ok, the setup. I have two queries. UpdateGrads and CurrentGradList. UpdateGrads in a non-updateable query. CurrentGradList is an updateable query. There are 7 records in currentGradList and 2 records in updateGrads. (kept the queries small for testing.) The code below is what I use (and have used on several occasions before.) to update a non-updateable query. (Basically updating the second query based of the records of the non-updateable one.)

    The problem. The second loop appears to be infinite. Even though I am 100% positive there are only 2 records in the query, it will continue to loop until I manually break it. Here's what happends. I run the code, msgboxes pop up in the following order

    RS2 0
    RS 0
    RS 1
    RS 2
    RS 3
    RS 4
    RS 4
    ... and so on until i break it.

    Anyone see my error in the code below, I've been racking my brain trying to find it and I'm coming up with squat..
    Code:
    Dim DB As DAO.Database
        Dim RS As DAO.Recordset
        Dim RS2 As DAO.Recordset
        Dim i As Integer
        Dim x As Integer
        
        Set DB = CurrentDb
        Set RS = DB.OpenRecordset("UPdateGrads")
        Set RS2 = DB.OpenRecordset("CurrentGradList")
        
        If RS.RecordCount <> 0 Then
        RS.MoveFirst
        RS2.MoveFirst
        
        Do While Not RS2.EOF
          MsgBox "RS2 " & i
          Do While Not RS.EOF
            MsgBox "RS " & x
            If RS.Fields("StudentID") = RS2.Fields("StudentID") Then
               RS2.Edit
               RS2.Fields("StatusCode") = 201
               RS2.Fields("NextClass") = Null
               RS2.Fields("CurrentInstructor") = Null
               RS2.Fields("req1") = Null
               RS2.Fields("req2") = Null
               RS2.Fields("req3") = Null
               RS2.Fields("req4") = Null
               RS2.Fields("req5") = Null
               RS2.Fields("req6") = Null
               RS2.Fields("req7") = Null
               RS2.Fields("LDA") = RS2.Fields("GradDate")
               RS2.Update
            End If
            x = x + 1
            RS.MoveNext
         Loop
         i = i + 1
         x = 0
         RS.MoveFirst
         RS2.MoveNext
        Loop
        
        End If
        Set DB = Nothing
        Set RS = Nothing
        Set RS2 = Nothing
        
        MsgBox "Done"

  2. #2
    SoftwareMatters is offline Contributing User
    Join Date
    May 2009
    Location
    Somerset, UK
    Posts
    131
    Rep Power
    91
    At first glance I can't see how you would get those message boxes in that order and stuck in a loop. What happens if you break the code and then step through it a line at a time - it might give you a clue as to where it's going wrong?
    Regards

    JD

    Software matters - Bespoke Access Database Design and Development

  3. #3
    meratigoerr's Avatar
    meratigoerr is offline Contributing User
    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    I've narrowed the problem down to the first recordset (RS). it's coming up empty. I have no idea why though, I'm 100% sure that query returns 2 records. I've given up on this method for now. Time does not allow me to pursue it further at this point. I went back to the more traditional route of using a temporary table to update my records. I was trying this method just to see if I could do it. Guess I'll have to find out later.

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

Similar Threads

  1. New recordset within a loop?
    By rtown in forum ASP Development
    Replies: 6
    Last Post: February 25th, 2011, 02:46 PM
  2. Trouble in Loop
    By kears_falcon_9 in forum ASP Development
    Replies: 2
    Last Post: March 1st, 2007, 05:09 AM
  3. having trouble breaking Loop
    By Bigced in forum ASP Development
    Replies: 6
    Last Post: July 11th, 2005, 03:48 PM
  4. HELP With ASP Do Loop and Recordset
    By plshelp in forum ASP Development
    Replies: 3
    Last Post: April 29th, 2005, 10:05 AM
  5. Replies: 5
    Last Post: December 4th, 2004, 10:04 AM

ASP Free Advertisers and Affiliates