Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Loop without Do Error

Posted on 2009-05-04
7
Medium Priority
?
788 Views
Last Modified: 2012-05-06
Hello:

I am trying to assign a team to review another team progress. However one team cannot have more than 1 team to review. When I run the script it gives a Loop without Do Error
strSQLAccess11 = "SELECT Distinct TeamName FROM Data WHERE Selected = 'No' Order by TeamName"
rsAccess11.Open strSQLAccess11, cnAccess11, strDBCursorType, strDBLockType, strDBOptions
 
If rsAccess11.EOF Then
MsgBox "All Done!"
Else
Do While Not rsAccess11.EOF
TeamName = rsAccess11("TeamName")
 
strSQLAccess12 = "SELECT Data.TeamName, AdjNameDept.Dept FROM Data INNER JOIN AdjNameDept ON Data.TeamName = AdjNameDept.Fullteamname WHERE TeamName <> '" & TeamName & "'"
rsAccess12.Open strSQLAccess12, cnAccess12, strDBCursorType, strDBLockType, strDBOptions
If rsAccess12.EOF Then
strSQLAccess13 = "UPDATE Data Set TeamReviewer = 'No Team Reviewer' WHERE TeamName = '" & TeamName & "'"
rsAccess13.Open strSQLAccess13, cnAccess13, strDBCursorType, strDBLockType, strDBOptions
Else
TeamReviewer = rsAccess12("TeamName")
strSQLAccess14 = "SELECT TeamReviewer FROM Data WHERE TeamReviewer = '" & TeamReviewer & "'"
rsAccess14.Open strSQLAccess14, cnAccess14, strDBCursorType, strDBLockType, strDBOptions
If Not rsAccess14.EOF Then
rsAccess11.MoveNext
Loop
Else
TeamReviewer = rsAccess14("TeamReviewer")
strSQLAccess15 = "UPDATE Data Set TeamReviewer = '" & TeamReviewer & "', Selected = 'Yes' WHERE TeamName = '" & TeamName & "'"
rsAccess15.Open strSQLAccess15, cnAccess15, strDBCursorType, strDBLockType, strDBOptions
rsAccess11.MoveNext
Loop
End If
End If
End If

Open in new window

0
Comment
Question by:RecipeDan
7 Comments
 
LVL 75
ID: 24297721
Looks like you have an extra Loop on line 21 or 27

mx
0
 
LVL 1

Author Comment

by:RecipeDan
ID: 24298106
I tried removing them one at a time and still get the same error.

In line 21 if the TeamReviewer is already assigned a team I want the script to go to the next record (rsAccess11)

In line 27 after the TeamReviewer is assigned a team I  want the script to go to the next record (rsAccess11)

Does this make sense?
0
 
LVL 75
ID: 24298142
Well, either way ... one of the Loop statements has to go ... you can only have one per Do.

mx
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75
ID: 24298352
"I tried removing them one at a time and still get the same error. "

Besides the extra Loop ... you can still get that error if the

IF   ELSE   and END IF 's are not balanced.  

mx
0
 
LVL 6

Expert Comment

by:jparul
ID: 24299344
Besides having an extra loop, the end ifs for your if statement are outside the loop statement.
This is the reason it is giving the error.
0
 
LVL 4

Accepted Solution

by:
nmilmine earned 2000 total points
ID: 24301100
This code doesn't cause any errors and appears to be along the line of what you are after.  Using tabbing will help to make the code easier to read
 
    Do While Not rsAccess11.EOF
        TeamName = rsAccess11("TeamName")
        strSQLAccess12 = "SELECT Data.TeamName, AdjNameDept.Dept FROM Data INNER JOIN AdjNameDept ON Data.TeamName = AdjNameDept.Fullteamname WHERE TeamName <> '" & TeamName & "'"
        rsAccess12.Open strSQLAccess12, cnAccess12, strDBCursorType, strDBLockType, strDBOptions
        Do While Not rsAccess12.EOF
            If rsAccess12.EOF Then
                strSQLAccess13 = "UPDATE Data Set TeamReviewer = 'No Team Reviewer' WHERE TeamName = '" & TeamName & "'"
                rsAccess13.Open strSQLAccess13, cnAccess13, strDBCursorType, strDBLockType, strDBOptions
                Do While Not rsAccess13.EOF
                    'Code for rsAccess13
                rsAccess13.MoveNext
                Loop
            Else
                TeamReviewer = rsAccess12("TeamName")
                strSQLAccess14 = "SELECT TeamReviewer FROM Data WHERE TeamReviewer = '" & TeamReviewer & "'"
                rsAccess14.Open strSQLAccess14, cnAccess14, strDBCursorType, strDBLockType, strDBOptions
                Do While Not rsAccess14.EOF
                    If Not rsAccess14.EOF Then
                        TeamReviewer = rsAccess14("TeamReviewer")
                        strSQLAccess15 = "UPDATE Data Set TeamReviewer = '" & TeamReviewer & "', Selected = 'Yes' WHERE TeamName = '" & TeamName & "'"
                        rsAccess15.Open strSQLAccess15, cnAccess15, strDBCursorType, strDBLockType, strDBOptions
                        Do While Not rsAccess15.EOF
                            'Code for rsAccess15
                        rsAccess15.MoveNext
                        Loop
                    End If
                rsAccess14.MoveNext
                Loop
            End If
            rsAccess12.MoveNext
            Loop
    rsAccess11.MoveNext
    Loop
   
    MsgBox "All Done!"
0
 
LVL 1

Author Comment

by:RecipeDan
ID: 24306944
Thank you. I figured out a similar solution to nmilmine
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question