Help on vb

[Taftheman]

Hi i have the following code to populate a grid but i am having difficulty getting the sql right, can some one please help


code/

rs1.Open "Select Ident, School_Ident from pupil where school_Ident is not null and school_Ident <>0", cn
   
  Do While Not rs1.EOF
      rs1.MoveNext
         If rs2.State = adStateOpen Then
            rs2.Close
         End If
         rs2.Open "Select top 1ID from Table where ID = " & rs1!Ident & " order " & _
  "By Date DESC", cn
               If rs1!School_Ident <> rs2Id Then
                  If strNo = "" Then
                     strNo = rs1!Ident
                  Else
                     strNo = strNo & " OR P.Ident = " & rs1!Ident
                  End If
               End If
   Loop
   
   strSQL = "SELECT P.Ident, P.Forename, P.Surname, PS.Start_Date FROM Pupil P " & _
            "Left Join Pupil_School PS On P.Ident = PS.Ident " & _
            "WHERE P.Ident = " & strNo & " and end_date is null ORDER BY P.surname + P.Forename"

/Code

is there an easier way as i keep getting overflow

[willakawill]

Hi. Try moving this statement to the end of the loop:

[INDENT]rs1.MoveNext[/INDENT]
Loop

You are reaching the EOF condition at the beginning of the loop and trying to process a non existent row.