Solved

Loop without Do Error

Posted on 2009-05-04
7
774 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

739 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