Solved

Loop without Do Error

Posted on 2009-05-04
7
769 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

758 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now