Nested Do Loops

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Do Loop nested in another Do Loop like so:

Do until x = i
(Code)
Do until x1 = i1
(Code)
Loop

Loop

For some reason whe I put in a code break, the procedure works correctly;
however, take out the code break and the procedure runs only once instead of
the 600 times I need it to run.

My purpose is to loop through the records in a sorted table and update null
fields with a value. Let me know if you need more detail. Thanks.
 
Hi Jeremy,

I don't know what you mean by "a code break". Also, your snippet doesn't
assign initial values to x, x1, i and i1, or increment x and x1 in their
respective loops.
 
Sorry John, here is my code (keep in mind I'm no professional programmer):

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rst1 As New ADODB.Recordset
Dim rst3 As New ADODB.Recordset
Dim i As Long
Dim i1 As Long
Dim i3 As Long
Dim host_name As String
Dim sql As String
Dim sql1 As String
Dim sql2 As String
Dim sql3 As String

Set cnn = CurrentProject.Connection

rst.ActiveConnection = cnn
sql = "SELECT StrConv([last],3) & ', ' & StrConv([first],3) AS Host_Name,
Int((11-1+1)*Rnd([userid])+1) AS RandNum FROM Host_List;"
sql3 = "SELECT COUNT(*) AS Players FROM Host_ WHERE Host IS NULL"

rst3.Open sql3, cnn, adOpenKeyset, adLockPessimistic
i3 = rst3!players

rst.CursorLocation = adUseClient
rst.Open sql, cnn, 3, 3
rst.Sort = "RandNum"

i = rst.RecordCount

Do Until x3 = i3

Do Until x = i

sql1 = "SELECT TOP 1 PName FROM Host_ WHERE Host IS NULL ORDER BY tpd,
RandNum;"
rst1.CursorLocation = adUseClient
rst1.Open sql1, cnn, 3, 3

sql2 = "UPDATE Host_ SET Host = '" & rst!host_name & "' WHERE PName ='"
& rst1!PName & "'"
cnn.Execute sql2

rst1.Close

rst.MoveNext

x = x + 1

Loop

x3 = x3 + 1

Loop

rst.Close
rst3.Close
 
Hi Jeremy,

I'm not a professional programmer either (they're in the minority in
these forums), but I've reformatted some of your code below and added
some comments to make it easier for me to follow. Be sure to comment
your code as you write it, and try to use self-explanatory variable
names; otherwise you'll find it hard in two or five years time to
remember the difference between sql, sql1, sql2, sql3 and so on.

I suspect that the problem is this line:

i = rst.RecordCount

I'm not an ADO expert, but as far as I know you can't rely on
RecordCount to give you the actual number of records in the recordset
until Access has read the entire recordset. You can force this by doing

rst.MoveLast
rst.MoveNext
i = rst.RecordCount

Sorry John, here is my code (keep in mind I'm no professional programmer):

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rst1 As New ADODB.Recordset
Dim rst3 As New ADODB.Recordset
Dim i As Long
Dim i1 As Long
Dim i3 As Long
Dim host_name As String
Dim sql As String
Dim sql1 As String
Dim sql2 As String
Dim sql3 As String

Set cnn = CurrentProject.Connection
Click to expand...
rst.ActiveConnection = cnn
sql = "SELECT " _
& StrConv([last],3) & ', ' & StrConv([first],3) AS Host_Name, " _
& Int((11-1+1)*Rnd([userid])+1) AS RandNum FROM Host_List;"
sql3 = "SELECT COUNT(*) AS Players FROM Host_ WHERE Host IS NULL;"

rst3.Open sql3, cnn, adOpenKeyset, adLockPessimistic
i3 = rst3!players

rst.CursorLocation = adUseClient
rst.Open sql, cnn, 3, 3
rst.Sort = "RandNum"

i = rst.RecordCount

Do Until x3 = i3 'outer loop

Do Until x = i 'inner loop
sql1 = "SELECT TOP 1 PName FROM Host_ " _
& "WHERE Host IS NULL ORDER BY tpd, RandNum;"
rst1.CursorLocation = adUseClient
rst1.Open sql1, cnn, 3, 3

sql2 = "UPDATE Host_ SET Host = '" & rst!host_name _
& "' WHERE PName ='" & rst1!PName & "';"
cnn.Execute sql2
rst1.Close
rst.MoveNext

x = x + 1
Loop 'inner

x3 = x3 + 1
Loop 'outer
rst.Close
rst3.Close
 
Thanks for your help John, but I think I figured it out. In my nested loop I
needed to reset the counter to x = 0. What was happening was the loops were
working but since "Do Until x = i" was satisfied, the inner loop didn't
perform its procedures and the out loop kept cycling through. Thanks again.

John Nurick said:
Hi Jeremy,

I'm not a professional programmer either (they're in the minority in
these forums), but I've reformatted some of your code below and added
some comments to make it easier for me to follow. Be sure to comment
your code as you write it, and try to use self-explanatory variable
names; otherwise you'll find it hard in two or five years time to
remember the difference between sql, sql1, sql2, sql3 and so on.

I suspect that the problem is this line:

i = rst.RecordCount

I'm not an ADO expert, but as far as I know you can't rely on
RecordCount to give you the actual number of records in the recordset
until Access has read the entire recordset. You can force this by doing

rst.MoveLast
rst.MoveNext
i = rst.RecordCount

Sorry John, here is my code (keep in mind I'm no professional programmer):

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rst1 As New ADODB.Recordset
Dim rst3 As New ADODB.Recordset
Dim i As Long
Dim i1 As Long
Dim i3 As Long
Dim host_name As String
Dim sql As String
Dim sql1 As String
Dim sql2 As String
Dim sql3 As String

Set cnn = CurrentProject.Connection
Click to expand...
rst.ActiveConnection = cnn
sql = "SELECT " _
& StrConv([last],3) & ', ' & StrConv([first],3) AS Host_Name, " _
& Int((11-1+1)*Rnd([userid])+1) AS RandNum FROM Host_List;"
sql3 = "SELECT COUNT(*) AS Players FROM Host_ WHERE Host IS NULL;"

rst3.Open sql3, cnn, adOpenKeyset, adLockPessimistic
i3 = rst3!players

rst.CursorLocation = adUseClient
rst.Open sql, cnn, 3, 3
rst.Sort = "RandNum"

i = rst.RecordCount

Do Until x3 = i3 'outer loop

Do Until x = i 'inner loop
sql1 = "SELECT TOP 1 PName FROM Host_ " _
& "WHERE Host IS NULL ORDER BY tpd, RandNum;"
rst1.CursorLocation = adUseClient
rst1.Open sql1, cnn, 3, 3

sql2 = "UPDATE Host_ SET Host = '" & rst!host_name _
& "' WHERE PName ='" & rst1!PName & "';"
cnn.Execute sql2
rst1.Close
rst.MoveNext

x = x + 1
Loop 'inner

x3 = x3 + 1
Loop 'outer
rst.Close
rst3.Close
Click to expand...
 
Back
Top