Error Wend without While

[Guest]

I have three nested loops. I get in error on inner while loop:
While Not rscount.EOF

The error states "Wend without While". The code is below.

I'm not sure what I'm doing wrong... Does anyone see anything?

Thanks in Advance!
Elena




StrMonth = 1

While Not StrMonth = 13


strYear = 2002

While Not strYear = 2009

strCount = 0
strTotal = 0

strsql = "Select * from TempTransfer where TransMonth = " & StrMonth & " and
TransYear = " & strYear
Set rscount = CurrentDb.OpenRecordset(strsql)


rscount.MoveFirst

While Not rscount.EOF
If rscount.RecordCount > 0 Then

strDiff = rscount!Difference
strTotal = strTotal + strDiff
strCount = strCount + 1


rscount.MoveNext
Wend

average = strDiff / strTotal

strsql = "Insert into TempAverage values (" & StrMonth & ", " & strYear & ",
" & average & ")"
CurrentDb.Execute (strsql)

End If

strYear = strYear + 1
Wend



StrMonth = StrMonth + 1
Wend

 

[Brendan Reynolds]

If you indent the code and remove the superflous blank lines, the problem
becomes immediatly visible. Look at the lines I've marked with comments
('<---?') in the reformatted code below ...

Public Sub TestWend()

StrMonth = 1
While Not StrMonth = 13
strYear = 2002
While Not strYear = 2009
strCount = 0
strTotal = 0
strSQL = "Select * from TempTransfer where TransMonth = " & StrMonth
& " and "
TransYear = " & strYear"
Set rscount = CurrentDb.OpenRecordset(strSQL)
rscount.MoveFirst
While Not rscount.EOF
If rscount.RecordCount > 0 Then
strDiff = rscount!Difference
strTotal = strTotal + strDiff
strCount = strCount + 1
rscount.MoveNext
Wend '<---?
average = strDiff / strTotal
strSQL = "Insert into TempAverage values (" & StrMonth & ", " &
strYear & ", " & average & ")"
CurrentDb.Execute (strSQL)
End If '<---?
strYear = strYear + 1
Wend
StrMonth = StrMonth + 1
Wend

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Guest]

Thank you! Sometimes you just need another pair of eyes.

 

[Brendan Reynolds]

With respect, Elena, I disagree. It was not the other pair of eyes that made
the difference, it was the way I formatted the code.

Of course, it's your code, it's your choice. This is just in the nature of
friendly advice, feel free to ignore it if you wish. But I strongly
recommend reviewing the VBA coding standards at www.xoc.net. Note I'm
referring to the coding conventions here rather than the better-known naming
conventions (I do recommend them too, but that's a separate issue).

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.