Endless Loop - Why?

[-Karl]

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

 

[Dirk Goldgar]

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

 

[-Karl]

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!