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

    Join Date
    Jun 2011
    South Jersey
    Rep Power

    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..
    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
        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.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")
            End If
            x = x + 1
         i = i + 1
         x = 0
        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
    Somerset, UK
    Rep Power
    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?


  4. #3
  5. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    South Jersey
    Rep Power
    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, 03:46 PM
  2. Trouble in Loop
    By kears_falcon_9 in forum ASP Development
    Replies: 2
    Last Post: March 1st, 2007, 06:09 AM
  3. having trouble breaking Loop
    By Bigced in forum ASP Development
    Replies: 6
    Last Post: July 11th, 2005, 04:48 PM
  4. HELP With ASP Do Loop and Recordset
    By plshelp in forum ASP Development
    Replies: 3
    Last Post: April 29th, 2005, 11:05 AM
  5. Replies: 5
    Last Post: December 4th, 2004, 11:04 AM

IMN logo majestic logo threadwatch logo seochat tools logo