Solved loop without do

[FahadTiger]

hi experts....
Can anyone tell me why I get an error message "loop without do"? for this code

Do While Not rs2.EOF
        For j = LBound(MYarray) To UBound(MYarray)
            If rs2.Fields("ISSUE").Value = MYarray(j) Then
                store = store & "," & rs2.Fields("ENGINEER").Value
            Else
            End If
            rs2.MoveNext
Loop

 

[LarryE]

hi experts....
Can anyone tell me why I get an error message "loop without do"? for this code

Do While Not rs2.EOF
        For j = LBound(MYarray) To UBound(MYarray)
            If rs2.Fields("ISSUE").Value = MYarray(j) Then
                store = store & "," & rs2.Fields("ENGINEER").Value
            Else
            End If
            rs2.MoveNext
Loop

Try using a Next statement instead of MoveNext. Is this all the code? Are you opening a recordset first and then executing these lines? Just asking.

Do While Not rs2.EOF
        For j = LBound(MYarray) To UBound(MYarray)
            If rs2.Fields("ISSUE").Value = MYarray(j) Then
                store = store & "," & rs2.Fields("ENGINEER").Value
            Else
            End If
        Next
Loop

 

[CJ_London]

you still need the movenext otherwise rs2 will never reach EOF. And you need the next for the j

Do While Not rs2.EOF
        For j = LBound(MYarray) To UBound(MYarray)
            If rs2.Fields("ISSUE").Value = MYarray(j) Then
                store = store & "," & rs2.Fields("ENGINEER").Value
            Else
            End If
            rs2.movenext
        Next j
Loop

 

[MajP]

Can anyone tell me why I get an error message "loop without do"? for this code

If you have a broken construct (IF without END IF, Do without Loop, For without Next) and they are nested the error always seems to be the outer nest and not the construct with the actual problem. So the message always seems wrong.
In this case it is broken because you have a FOR without a Next inside the DO Loop. You would expect the error to be FOR WITHOUT NEXT, not LOOP Without DO. Only after seeing this a few times do you know what to look for.

 

[KitaYama]

the error always seems to be the outer nest

I've always wondered why vba shows the wrong message. I think this clarifies my doubt.

 

[theDBguy]

I've always wondered why vba shows the wrong message. I think this clarifies my doubt.

On the other hand, I think it's actually giving you the correct message. Of course, I am only guessing, but I believe the VBA interpreter/compiler is reading your code from top to bottom and probably went like this:

Do While Not ... (compiler: Ah, we're starting a Do loop, need to keep an eye out for the closing Loop keyword)
    For j = ... (compiler: Ah, now we're starting a For/Next loop, need to keep an eye out for the closing Next keyword)
        If rs2.Fields ... (compiler: Ah, this time we're starting an If/Then block, need to keep an eye out for the closing End If statement)
            store = store & ... (compiler: nothing wrong here, just move on)
        Else (compiler: I haven't seen the End If yet, so this Else statement is okay)
        End If (compiler: Ah, here's the end of the If/Then block)
        
        rs2.MoveNext (compiler: This should be legal within the For/Next loop, proceed)
        Loop (compiler: Wait a minute, I didn't see a beginning Do statement inside the For/Next loop, this must be a mistake. Show error message)
[here's the rest of the code that's missing from the original post]
    Next
Loop

See how the indentation helps see the problem? I certainly hope so. Cheers!

 

[KitaYama]

On the other hand, I think it's actually giving you the correct message. Of course, I am only guessing, but I believe the VBA interpreter/compiler is reading your code from top to bottom and probably went like this:

Do While Not ... (compiler: Ah, we're starting a Do loop, need to keep an eye out for the closing Loop keyword)
    For j = ... (compiler: Ah, now we're starting a For/Next loop, need to keep an eye out for the closing Next keyword)
        If rs2.Fields ... (compiler: Ah, this time we're starting an If/Then block, need to keep an eye out for the closing End If statement)
            store = store & ... (compiler: nothing wrong here, just move on)
        Else (compiler: I haven't seen the End If yet, so this Else statement is okay)
        End If (compiler: Ah, here's the end of the If/Then block)
       
        rs2.MoveNext (compiler: This should be legal within the For/Next loop, proceed)
        Loop (compiler: Wait a minute, I didn't see a beginning Do statement inside the For/Next loop, this must be a mistake. Show error message)
[here's the rest of the code that's missing from the original post]
    Next
Loop

See how the indentation helps see the problem? I certainly hope so. Cheers!

I'm sorry but I can't understand your point. Here's a simplified version of your code used above:

Sub test()
    Do While Not f = 0
        For j = 1 To 10
            Loop
        Next
    Loop
End Sub

Vba gives me Loop without DO error and the error message makes sense. Because I don't have a matching Do/loop.

But in case of OP's question or similar problems I've had before, the message says there's a problem with loop. While actually loop is OK and the problem is For without Next.

 

[The_Doc_Man]

@FahadTiger - I will explain your error message. In that explanation, I SHOULD use code tags but I can't colorize them so I'll do something else.

Do While Not rs2.EOF
For j = LBound(MYarray) To UBound(MYarray)
If rs2.Fields("ISSUE").Value = MYarray(j) Then
store = store & "," & rs2.Fields("ENGINEER").Value
Else
End If
rs2.MoveNext
Loop

The DO WHILE ... LOOP construct is almost OK - except that the FOR loop construct is incomplete. More specifically, the FOR J=... loop is unterminated. The correct termination would be to put a NEXT J just below the rs2.MoveNext instruction. You would NOT use a simple NEXT statement because that is not the correct termination. The syntax of VBA FOR "stepping" loops requires that you not only have the stepping variable in the FOR statement but ALSO in the NEXT statement.

This error is due to the "code block" concept. I colorized to show the different blocks. The IF/THEN/ELSE/END IF block in PURPLE is perfectly legal and properly terminated (though you could omit the ELSE and it wouldn't change anything.) This is not the cause of the problem.

The GREEN block is unterminated because of not having the NEXT J in place. Therefore, the LOOP statement doesn't have a corresponding DO statement in the same code block. My colors show what you probably intended, but using the code block concept, that LOOP statement appears (to VBA) to be in the GREEN block even though the corresponding DO WHILE was in the RED block. Thus, in the block where it appears, you have a LOOP without a DO. Adding the NEXT J before the LOOP statement would close out the GREEN block and leave you back in the RED block where the LOOP would properly terminate the DO.

Just one more side note: rs2.Fields("ISSUE").Value is a bit torturous. You could have used rs2![ISSUE] just as easily. Ditto for the [Engineer] field. Turns out that for anything that HAS a .Value property, it is the default property and thus does not need to be expressed. AND if that recordset has a field named ISSUE or one called ENGINEER, then the rs2![fieldname] syntax gets you there without quite as much typing. Again, you can omit the FIELDS() element because the default property for a recordset IS the FIELDS collection.

 

[SHANEMAC51]

Can anyone tell me why I get an error message "loop without do"? for this code

Do While Not rs2.EOF
For j = LBound(MYarray) To UBound(MYarray)
If rs2.Fields("ISSUE").Value = MYarray(j) Then
store = store & "," & rs2.Fields("ENGINEER").Value
Else
End If
next j
rs2.MoveNext
Loop
''some actions with a variable

debug.print store

 

[Auntiejack56]

I love the way the DBGuy's compiler gives a satisfied "ah" every time it encounters an understandable piece of code. I hope my compiler is enjoying itself even half as much.

 

[FahadTiger]

you still need the movenext otherwise rs2 will never reach EOF. And you need the next for the j

Do While Not rs2.EOF
        For j = LBound(MYarray) To UBound(MYarray)
            If rs2.Fields("ISSUE").Value = MYarray(j) Then
                store = store & "," & rs2.Fields("ENGINEER").Value
            Else
            End If
            rs2.movenext
        Next j
Loop

thanks,its solve it

 

[CJ_London]

more simplistically the way I think of it is - from the original code

Sub test()
    Do While Not f = 0---compiler says OK starting a loop
        For j = 1 To 10---compiler says OK  starting a loop
            code
        
    Loop---compiler looks back as far as the last 'loop start' and can't see a Do
End Sub

 

[KitaYama]

Sub test()
     
    Loop---compiler looks back as far as the last 'loop start' and can't see a Do
End Sub

Sorry but I can't understand this. You have a Do on line 2. Why compiler can't see it?

If I was the compiler I would think this way:

Sub test()
    Do While Not f = 0---compiler says OK starting a loop
        For j = 1 To 10---compiler says OK  starting a loop
            code
    
    Loop---compiler says I reached the loop for Do on line 2 but there's no next. 
                                       Do and For are overlapping. Send a message For without Next
End Sub--

 

[MajP]

The correct termination would be to put a NEXT J just below the rs2.MoveNext instruction. You would NOT use a simple NEXT statement because that is not the correct termination. The syntax of VBA FOR "stepping" loops requires that you not only have the stepping variable in the FOR statement but ALSO in the NEXT statement

This is not correct. VBA is very loose on this construct. No variable is needed in the NEXT. The below runs fine.

Public Sub TestLoop()
  Dim rs As DAO.Recordset
  Dim i As Integer
  Dim j As Integer
  Dim fld As DAO.Field
 
  Set rs = CurrentDb.OpenRecordset("01Schools", dbOpenDynaset)
 
  For Each fld In rs.Fields
    For i = 1 To 3
      For j = 1 To 4
        Debug.Print fld.Name & " I: " & i & " J:" & j
      Next
    Next
  Next
End Sub

 

[CJ_London]

You have a Do on line 2. Why compiler can't see it?

My thinking is based on how the compiler actually works, not how you think it should work. whether you use for-next, while-loop, etc, they are all loops - the compiler just looks for the start of a loop, not the one that 'matches'.

If it could, then the error would be on the previous line (where next is missing) but when it reaches that point it doesn't know that is where the 'next' should be so cannot generate an error.

If it compiled in a different way accounting for the different loop types you might have -
started a 'do' loop so keep an eye out for the 'loop' terminator
now started a 'for' loop so keep an eye out for the 'next' terminator
now started another 'for' loop so keep an eye out for another 'next' loop
found the 'loop' terminator but hey, haven't seen a 'next' terminator so which 'for' is missing a 'next'?

and now you have the problem - there are two for/next loops and the developer has missed the first next. So when the compiler reaches a next - which for is it applied to?

 

[KitaYama]

and now you have the problem - there are two for/next loops and the developer has missed the first next. So when the compiler reaches a next - which for is it applied to?

I think I understand your logic now.
But think it this way : In the following line,

Debug.Print Left(myvar, Len(Replace(var2, "_", "") - Len(var2))) & Mid(var2, Len(var2) - Len(var1))

the compiler has an eye on all opening and closing parentheses and quotation marks.
Not only it counts the number of closing and opening parentheses, but also checks for matching pairs and weather they are in the right position.

Why the compiler can not do the same for loops?

If my memory serves me well (which recently doesn't) long long time ago, when I wrote php, the compiler was able to spot which inner loop has not a matching pair and highlighted the block. Then I was using Zend editor. I wonder why VBA behaves the way you explained.

 

[NauticalGent]

This is not correct. VBA is very loose on this construct. No variable is needed in the NEXT. The below runs fine.

Doc is reminiscing about his BASIC days...

 

[NauticalGent]

I wonder why VBA behaves the way you explained.

More correctly: "I wonder why VBA (mis)behaves the way you explained."

 

[CJ_London]

the compiler has an eye on all opening and closing parentheses and quotation marks.
Not only it counts the number of closing and opening parentheses, but also checks for matching pairs and weather they are in the right position.

I would suggest because that is all one line of code so will be compiled in one go.

Thinking 'out loud' they belong to functions with parameters separated by commas so the compiler knows how many parameters the function has so it knows where to expect to find the closing parentheses.

And before you mention optional and paramarray parameters I would say it knows the minimum number of commas so expects a closing parentheses after that. With nested functions, it still remains a relatively simple algorithm to keep track.

When you get a compile error - does it actually tell you which closing parentheses is missing?

Within a loop it is anyone's guess as to where the loop ends until the code says so.

Edit: FYI I have developed a sql editor/management app (being demoed tomorrow at Devcon) and I use a similar method to the above to track what is between single and double quotes and other pairings for the purposes of formatting

 

[The_Doc_Man]

MajP said: This is not correct. VBA is very loose on this construct. No variable is needed in the NEXT. The below runs fine.

From the Microsoft VBA Language Specification published 04-30-2014, top of page 77 (in the PDF version thereof), and in section 5.4.2.3 For Statement I offer a couple of excerpts.

for-statement = simple-for-statement / explicit-for-statement
simple-for-statement = for-clause EOS statement-block "Next"
explicit-for-statement = for-clause EOS statement-block ("Next" / (nested-for-statement ",")) bound-variable-expression
nested-for-statement = explicit-for-statement / explicit-for-each-statement
for-clause = "For" bound-variable-expression "=" start-value "To" end-value [stepclause]
start-value = expression
end-value = expression
step-clause = Step" step-increment
step-increment = expression

The <bound-variable-expression> within the <for-clause> of an <explicit-for-statement> must resolve to the same variable as the <bound-variable-expression> following the <statement-block>. The declared type of <bound-variable-expression> must be a numeric value type or Variant.

I didn't pluck my comment out of thin air. The presence of code tags means I cannot colorize or change much else, so I must simply refer you to the definition for the explicit-for-statement and the for-clause. According to the strict definition of a FOR statement, you have the "FOR variable = ..." part followed by the statement block followed by Next and, if there is no intervening loop, the bound variable expression as in "NEXT variable" - and further, the variable with NEXT must resolve to the same variable as was used in the FOR statement.

I think we must at least agree that the FOR loop lacked proper termination. Whether by NEXT or NEXT J, it needed something it didn't have, and the rest of my explanation (about implied language blocks) was correct. @MajP, you may indeed be correct that newer versions of VBA are looser than used to be the case. However, if the OP is using Access before 2016, this standard should apply.

NG said: Doc is reminiscing about his BASIC days...

Actually, no. As it happens, I am quoting the strict statement that describes VBA syntax, and I have named the reference. It is too big for me to upload in its entirety and besides, you can look it up yourself if you have an urgent need to do so. The following link gives you a page to download PDF or WORD versions from 2014 through 2021.

[MS-VBAL]: VBA Language Specification

Specifies the VBA Language, which defines the implementation-independent and operating system-independent programming

docs.microsoft.com

I looked at the 2021 version for the FOR statement and the language definition has not changed. It does not indicate that the bound variable of the NEXT is optional. More precisely, the syntax of the language definition doesn't include a "leave it blank" option.