A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Endless Loop - Why?



 
 
Thread Tools Display Modes
  #1  
Old July 17th, 2009, 02:33 AM posted to microsoft.public.access.forms
-Karl
external usenet poster
 
Posts: 15
Default 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  
Old July 17th, 2009, 02:49 AM posted to microsoft.public.access.forms
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default 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  
Old July 17th, 2009, 02:57 AM posted to microsoft.public.access.forms
-Karl
external usenet poster
 
Posts: 15
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:43 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.