#1
  1. Contributing User
    ASP Adventurer (500 - 999 posts)

    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
  3. No Profile Picture
    Contributing User
    ASP Discoverer (100 - 499 posts)

    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
  4. #3
  5. Contributing User
    ASP Adventurer (500 - 999 posts)

    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.

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

IMN logo majestic logo threadwatch logo seochat tools logo