Solved

Loop without Do Error

Posted on 2009-05-04
7
770 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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, when working with VBA, learn some techniques for writing readable and easily maintained code.

863 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

24 Experts available now in Live!

Get 1:1 Help Now