Error: Record Not Found (within a loop that should prevent this)

Try:

If not rsSBSPstuds.EOF and not rsSBSPstuds.BOF Then

Also, the rsSBSPstuds.movenext will probably be better placed before the last End if...

Max.

Hi djdidge,

You need to change

lstvStuds.ListItems(p).SubItems(13) & subitems 14

to Date Value first?

When you are comparing text fields, make sure you filter out all spaces before and after your strings. Also, you could compare the upper case values instead of the original values.

Hi, try this:

on error resume next

Do While Not rsSBSPstuds.EOF  
     If rsSBSPstuds("S_REF") = lstvStuds.ListItems(p).text And _
        rsSBSPstuds("SBA_START_DATE") = cdate(lstvStuds.ListItems(p).SubItems(13)) And _
        rsSBSPstuds("SBA_END_DATE") = cdate(lstvStuds.ListItems(p).SubItems(14)) And _
        rsSBSPstuds("SBA_SP_REF") = lstvStuds.ListItems(p).SubItems(17) And _
        rsSBSPstuds("SBA_SB_REF") = lstvStuds.ListItems(p).SubItems(18) Then
       
        If lstvStuds.ListItems(p).SubItems(18) = "" Then
           If rsSBSPstuds("SBA_SB_REF") <> "" Then lstvStuds.ListItems(p).SubItems(18) = lstvStuds.ListItems(p).SubItems(18)
        End If
       
        rsSBSPstuds.MoveNext
     End If
Loop


regards.

I tried your first comment but no joy,
the second wouldn't fit in with my logic as i need to access the next record in the if statements...

Gary

OR

Do While Not rsSBSPstuds.EOF  
    If rsSBSPstuds("S_REF") = lstvStuds.ListItems(p).text And _
       rsSBSPstuds("SBA_START_DATE") = cdate(lstvStuds.ListItems(p).SubItems(13)) And _
       rsSBSPstuds("SBA_END_DATE") = cdate(lstvStuds.ListItems(p).SubItems(14)) And _
       rsSBSPstuds("SBA_SP_REF") = lstvStuds.ListItems(p).SubItems(17) And _
       rsSBSPstuds("SBA_SB_REF") = lstvStuds.ListItems(p).SubItems(18) Then
       
       If lstvStuds.ListItems(p).SubItems(18) = "" Then
          If rsSBSPstuds("SBA_SB_REF") <> "" Then lstvStuds.ListItems(p).SubItems(18) = lstvStuds.ListItems(p).SubItems(18)
       End If
       
    End If
    rsSBSPstuds.MoveNext

Loop

that was directed at "dreamMaster"... i've just seen all your comments appear!..

if rs.eof=false and rs.bof=false then
  while rs.eof=False
       'your code
       rs.MoveNext
  wend
end if

Usually I use a do while construction to loop through a recordset...

So:

If not rsSBSPstuds.eof and not rsSBSPstuds.bof then
  do while not rsSBSPstuds.eof
   If rsSBSPstuds("S_REF") = lstvStuds.ListItems(p).text And _
        rsSBSPstuds("SBA_START_DATE") = lstvStuds.ListItems(p).SubItems(13) And _
        rsSBSPstuds("SBA_END_DATE") = lstvStuds.ListItems(p).SubItems(14) And _
        rsSBSPstuds("SBA_SP_REF") = lstvStuds.ListItems(p).SubItems(17) And _
        rsSBSPstuds("SBA_SB_REF") = lstvStuds.ListItems(p).SubItems(18) Then
       
        If lstvStuds.ListItems(p).SubItems(18) = "" Then
           If rsSBSPstuds("SBA_SB_REF") <> "" Then lstvStuds.ListItems(p).SubItems(18) = lstvStuds.ListItems(p).SubItems(18)
        End If
    end if
    rsSBSPstuds.movenext
   loop
end if

Cheers,
Max.

This only needs to run once btw...

    For p = 1 To srecount
      With lstvStuds.ListItems.Add(, , rsSBSPstuds("S_REF"), , "D")
        .SubItems(1) = IIf(rsSBSPstuds("s_Forename_1") <> "", rsSBSPstuds("s_Forename_1"), "N/A")
        .SubItems(2) = IIf(rsSBSPstuds("s_Surname") <> "", rsSBSPstuds("s_Surname"), "N/A")
        .SubItems(3) = ""
        .SubItems(4) = ""
        .SubItems(5) = ""
        .SubItems(6) = ""
        .SubItems(7) = ""
        .SubItems(8) = ""
        .SubItems(9) = ""
        .SubItems(12) = IIf(rsSBSPstuds("S_DATE_OF_BIRTH") <> "", rsSBSPstuds("S_DATE_OF_BIRTH"), "N/A")
        .SubItems(13) = IIf(rsSBSPstuds("SBA_START_DATE") <> "", rsSBSPstuds("SBA_START_DATE"), "N/A")
        .SubItems(14) = IIf(rsSBSPstuds("SBA_END_DATE") <> "", rsSBSPstuds("SBA_END_DATE"), "N/A")
        .SubItems(15) = ""
        .SubItems(16) = ""
        .SubItems(17) = IIf(rsSBSPstuds("SBA_SP_REF") <> "", rsSBSPstuds("SBA_SP_REF"), "N/A")
        .SubItems(18) = IIf(rsSBSPstuds("SBA_SB_REF") <> "", rsSBSPstuds("SBA_SB_REF"), "N/A")
        .SubItems(19) = ""
        .SubItems(20) = ""
        .SubItems(21) = IIf(rsSBSPstuds("RESULT") <> "", rsSBSPstuds("RESULT"), "")
        .SubItems(22) = ""
       
        .Tag = IIf(flag, &HC0FFC0, &HFFFFFF)       '     'CStr(QBColor(i Mod 10))
       
        If rsSBSPstuds("SBA_STATUS") = "W" Then
          lstvStuds.ListItems(p).SmallIcon = 8
           .SubItems(19) = "Yes"
           .Tag = RGB(255, 150, 150) '&H8080FF
          Else
           .SubItems(19) = ""
        End If
       If flag Then
         flag = False
       Else
         flag = True
       End If
     End With
     
     
On Error Resume Next

Do While Not rsSBSPstuds.EOF
    If rsSBSPstuds("S_REF") = lstvStuds.ListItems(p).text And _
       rsSBSPstuds("SBA_START_DATE") = CDate(lstvStuds.ListItems(p).SubItems(13)) And _
       rsSBSPstuds("SBA_END_DATE") = CDate(lstvStuds.ListItems(p).SubItems(14)) And _
       rsSBSPstuds("SBA_SP_REF") = lstvStuds.ListItems(p).SubItems(17) And _
       rsSBSPstuds("SBA_SB_REF") = lstvStuds.ListItems(p).SubItems(18) Then
       
       If lstvStuds.ListItems(p).SubItems(18) = "" Then
          If rsSBSPstuds("RESULT") <> "" Then lstvStuds.ListItems(p).SubItems(18) = rsSBSPstuds("RESULT")
       End If
       
       rsSBSPstuds.MoveNext
    End If
Loop
      '//
  Next


A lot of this is irrelevant but it might show more perspective to the problem

If it only needs to be run once...you do not need to loop...and you also do not need to use rsSBSPstuds.movenext....

Max.

oops... the bottom bit is what i was trying from ryancys...

its already in a bigger loop... the for loop i've pasted up

simple logic!