Solved

Loop without Do Error

Posted on 2009-05-04
7
771 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 500 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

808 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