Loop Issue

[DMT Dave]

Hi Guy's frustrating this one as I have plenty of Recordset loops going on and for some reason I am sure I have got the sequence correct here but coming up with Compile Error Loop With out Do

I do Have a Do, a bit like ABBA, I do I do I Do I Do I Do have a Do

        Set rs = CurrentDb.OpenRecordset("Select * From tblEdit WHERE Status = '" & strStatus & "' And Source = '" & strSrc & "' And Customer = '" & strCustomer & "' ORDER BY DelTo;")

        Do While Not rs.EOF
            If rs.Fields("Status") = "Delivery" Then
                dtDelDate = DLookup("DeliveryDate", "tblAssign", "[Status] = '" & strStatus & "'")
                strDriver = DLookup("Driver", "tblAssign", "[Status] = '" & strStatus & "'")
                strVehicle = DLookup("Vehicle", "tblAssign", "[Status] = '" & strStatus & "'")
                strBody = strBody & rs.Fields("DelTo") & " - " & rs.Fields("Town") & " - " & rs.Fields("PostCode") & " - " & rs.Fields("LiftType") & " - " & _
            rs.Fields("SONumber") & " - " & rs.Fields("LiftNo") & " - " & rs.Fields("Status") & "Delivered: " & Format(dtDelDate, "ddd-dd-mmm-yyyy") & " Driver: " & strDriver & " Vehicle: " & strVehicle & "|"
            End If
            
            If rs.Fields("Status") = "Collection" Then
                dtDelDate = DLookup("CollectedDate", "tblCollections", "[Status] = '" & strStatus & "'")
                srtBody = strBody & rs.Fields("DelTo") & " - " & rs.Fields("Town") & " - " & rs.Fields("PostCode") & " - " & rs.Fields("LiftType") & " - " & _
                rs.Fields("SONumber") & " - " & rs.Fields("LiftNo") & " - " & rs.Fields("Status") & "Collected: " & Format(dtDelDate, "ddd-dd-mmm-yyyy") & "|"
            End If
            
            If rs.Fields("Status") <> "Delivery" Then
                If rs.Fields("Status") <> " Collection" Then
                srtBody = srtBody & rs.Fields("DelTo") & " - " & rs.Fields("Town") & " - " & rs.Fields("PostCode") & " - " & rs.Fields("LiftType") & " - " & _
                rs.Fields("SONumber") & " - " & rs.Fields("LiftNo") & " - " & rs.Fields("Status") & "|"
            End If
            
        rs.MoveNext
        Loop


        Debug.Print rs

[CJ_London]

use

While Not rs.EOF
    ....
     ....

Wend

or it could be due to an incomplete loop in other parts of your code which you have not shown

[davegri]

You're missing an 'End If' in the last 'If'

[Welshgasman]

You're missing an 'End If' in the last 'If'

Not that I could see?

[davegri]

Not that I could see?

Third paragraph has 2 IFs and one End If

[Welshgasman]

Third paragraph has 2 IFs and one End If

Aha, nice catch. Missed that completely , as did the O/P

That is what can happen when you do not indent correctly , hard to spot errors like that.

[davegri]

Aha, nice catch. Missed that completely , as did the O/P

That is what can happen when you do not indent correctly , hard to spot errors like that.

I like 'Select Case' instead of 'If's to avoid such confusion - much easier for me to visualize.

[CJ_London]

good spot - agree a case statement would be simpler

[Micron]

Saw the issue right away. This is because of improper indentation IMO which should have made it obvious - that and the error message almost always means that some sort of code block (With, IF, etc.) has not been terminated.

Do ...
   If rs.Fields("Status") <> "Delivery" Then  
     If rs.Fields("Status") <> " Collection" Then
       srtBody = srtBody & rs.Fields("DelTo") & " - " & rs.Fields("Town") & " - " & rs.Fields("PostCode") & " - " & rs.Fields("LiftType") & " - " & _
       rs.Fields("SONumber") & " - " & rs.Fields("LiftNo") & " - " & rs.Fields("Status") & "|"
     End If
     rs.MoveNext << line was improperly indented
   Should now be more obvious that End If is missing here <<
Loop

@DMTDave - HTH

[DMT Dave]

Thank you guy's, will now look at the woods through trees, sometimes you cant see it,

Select case would be something like ?

Select Case rs.Fields("Delivery")
strBody = strBody etc
Do OtherStuff
End Select

Select Case rs.Fields("Collection")
strBody = strBody etc
Do OtherStuff
End Select


?

[DMT Dave]

Micron, yes sometimes it isn't the exact procedure that pops up

ie: i have noticed many a time if a "If" is missing, a compile error can highlight loop as the error and not the fact that fat fingers Dave can miss the "End If"

[Micron]

You'd think that a missing If where there is an End If or a missing End If inside of any block (doesn't have to be a loop) would highlight the missing component. Instead, it "breaks" the hierarchy of one of the outer blocks. If memory serves, this would also raise an error about a missing End With:

With something
  do this with it
  do that with it
  If this Then
    do something
End With

[Welshgasman]

Thank you guy's, will now look at the woods through trees, sometimes you cant see it,

Select case would be something like ?

Select Case rs.Fields("Delivery")
strBody = strBody etc
Do OtherStuff
End Select

Select Case rs.Fields("Collection")
strBody = strBody etc
Do OtherStuff
End Select


?

No.
You do not have fields by that name.
You would Select Case on Status field

[Micron]

Missing the Case statements there?

[DMT Dave]

OK thank you all who has input opinions, always greatly appreciated

Did miss the end If which cured the problem, of course i know can't open if you open something you have to close it or or starting loops has to loop etc....

Last question while i have used both in the past as CJ London suggested

While Not rs.EOF    ....
     ....
 Wend

Is this the same or is Wend more reliable than Loop or does it depend on what your code is doing (looking for) (looping) (Cases) (If's) etc...

 Do Until rs.EOF
do stuff
Loop

So i guess i am asking Wend vs Loop ?