If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Endless Loop - Why?
See code below, My 2nd recordset is causing me a endless loop. I only
have 3 entries in a test Db that I am trying it on yet I get this endless loop? Any thoughts? Private Sub Command4_Click() ' Dimension Variables '---------------------------------------------------------- Dim rsNewTest As DAO.Recordset Dim dbl As DAO.Database Set dbl = CurrentDb() Dim sqlNewTest As String sqlNewTest = "Select ID, Site from Sites" Set rsNewTest = dbl.OpenRecordset(sqlNewTest, dbOpenDynaset) Do Until rsNewTest.EOF 'Display Table Names?? 'For Counter = 0 To rsNewTest.Fields.Count - 1 ' MsgBox rsNewTest.Fields(Counter).Name 'Next 'Does the record already exist? If Me.Text2 = rsNewTest.Fields("Site") Then 'MsgBox rsNewTest.Fields("ID") & " " & rsNewTest.Fields ("Site") SiteID = rsNewTest.Fields("ID") 'Populate the combobox based on the Site name sqlNewTest1 = "Select Server from Servers where SiteID = " & rsNewTest.Fields("ID") Set rsNewTest1 = dbl.OpenRecordset(sqlNewTest1, dbOpenDynaset) ' THIS PART CREATES AN ENDLESS LOOP!!! WHY??? Do Until rsNewTest1.EOF MsgBox rsNewTest1.Fields("Server") MyCount = MyCount + 1 If MyCount = 5 Then MsgBox "breaking loop" Exit Do End If Loop flag = 1 Exit Do End If rsNewTest.MoveNext Loop 'If the Flag = 0 then insert the value into the table If flag 1 Then Set db = CurrentDb db.Execute "INSERT INTO Sites (Site) VALUES ('" & Me.Text2 & "')" 'Else 'MsgBox "flag is 1" End If ' close the recordset dbl.Close Set rsNewTest = Nothing Set flag = Nothing End Sub |
#2
|
|||
|
|||
Endless Loop - Why?
"-Karl" wrote in message
... See code below, My 2nd recordset is causing me a endless loop. I only have 3 entries in a test Db that I am trying it on yet I get this endless loop? Any thoughts? Private Sub Command4_Click() ' Dimension Variables '---------------------------------------------------------- Dim rsNewTest As DAO.Recordset Dim dbl As DAO.Database Set dbl = CurrentDb() Dim sqlNewTest As String sqlNewTest = "Select ID, Site from Sites" Set rsNewTest = dbl.OpenRecordset(sqlNewTest, dbOpenDynaset) Do Until rsNewTest.EOF 'Display Table Names?? 'For Counter = 0 To rsNewTest.Fields.Count - 1 ' MsgBox rsNewTest.Fields(Counter).Name 'Next 'Does the record already exist? If Me.Text2 = rsNewTest.Fields("Site") Then 'MsgBox rsNewTest.Fields("ID") & " " & rsNewTest.Fields ("Site") SiteID = rsNewTest.Fields("ID") 'Populate the combobox based on the Site name sqlNewTest1 = "Select Server from Servers where SiteID = " & rsNewTest.Fields("ID") Set rsNewTest1 = dbl.OpenRecordset(sqlNewTest1, dbOpenDynaset) ' THIS PART CREATES AN ENDLESS LOOP!!! WHY??? Do Until rsNewTest1.EOF MsgBox rsNewTest1.Fields("Server") MyCount = MyCount + 1 If MyCount = 5 Then MsgBox "breaking loop" Exit Do End If Loop flag = 1 Exit Do End If rsNewTest.MoveNext Loop 'If the Flag = 0 then insert the value into the table If flag 1 Then Set db = CurrentDb db.Execute "INSERT INTO Sites (Site) VALUES ('" & Me.Text2 & "')" 'Else 'MsgBox "flag is 1" End If ' close the recordset dbl.Close Set rsNewTest = Nothing Set flag = Nothing End Sub Two factors contribute: 1. Your inner recordset loop has no statement "rsNewTest1.MoveNext", so the recordset never advances to the next record and rsNewTest1.EOF will never become true. So you won't break out of the loop by satisfying the "Do Until" condition. 2. You never initialize MyCount inside the outer loop, so after the first run through of that loop, MyCount will be 5. Then the next time through the loop, MyCount will be incremented to 6 before you test to see if it's equal to 5: If MyCount = 5 Then MsgBox "breaking loop" Exit Do End If You should have a statement: MyCount = 0 .... inside the outer loop and before entering the inner on. Also, just for safety's sake, it would be wise to test the count's value for greater-than-or-equal, rather than just equal: If MyCount = 5 Then -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#3
|
|||
|
|||
Endless Loop - Why?
I just saw that i forgot the movenext command and was just about to
update this thread. Thanks for the quick response. You just beat me to it. The MyCount variable was just for troubleshooting. I will be removing it now that my loop works. I'm just staring at this code too much and trying to convert an Excel 'database' LOL Thanks a million, I will have more questions soon enough! Two factors contribute: 1. Your inner recordset loop has no statement "rsNewTest1.MoveNext", so the recordset never advances to the next record and rsNewTest1.EOF will never become true. *So you won't break out of the loop by satisfying the "Do Until" condition. 2. You never initialize MyCount inside the outer loop, so after the first run through of that loop, MyCount will be 5. *Then the next time through the loop, MyCount will be incremented to 6 before you test to see if it's equal to 5: * * * * * * * * * * * *If MyCount = 5 Then * * * * * * * * * * * * * *MsgBox "breaking loop" * * * * * * * * * * * * * *Exit Do * * * * * * * * * * * *End If You should have a statement: * * MyCount = 0 ... inside the outer loop and before entering the inner on. *Also, just for safety's sake, it would be wise to test the count's value for greater-than-or-equal, rather than just equal: * * * * * * * * * * * *If MyCount = 5 Then -- Dirk Goldgar, MS Access MVPwww.datagnostics.com (please reply to the newsgroup) |
Thread Tools | |
Display Modes | |
|
|