By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,454 Members | 3,133 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,454 IT Pros & Developers. It's quick & easy.

Looping a recordset definition based on number from array

P: 21
Hi folks,

I am working on a routine that will select a sample of parcels from a table. Each parcel belongs to a census tract, and depending on which tract, a certain calculation is applied. The trick is that the math is only to be performed on a random sample of parcels within each tract, and the size of the sample varies for each tract. Therefore, I define a recordset with a variable where the size of the sample is a value in an array populated from another table, perform the action, and then loop back to redefine the recordset. I get a subscript out of range error, so that smells a bit like an array issue, but it is my sql string that is highlighted, down in line 44. I have option base 1 in the declarations section. Can anyone see the practical, or for that matter conceptual problem with passing a value to define how many records are requested in the sequel query?

Expand|Select|Wrap|Line Numbers
  1. Sub cycletest()
  2.  
  3.     Dim rstarray As ADODB.Recordset
  4.     Set rstarray = New ADODB.Recordset
  5.     Dim rst As ADODB.Recordset
  6.     Set rst = New ADODB.Recordset
  7.     Dim strsqlarray As String
  8.     Dim strsql As String
  9.     Dim singleunitsadd(176, 2)
  10.     Dim i As Integer
  11.     Dim j As Integer
  12.     Dim k As Integer
  13.  
  14.     '1. Populate array
  15.     strsqlarray = "SELECT comparison.tractnumber as tractnumber, comparison.d1 AS diff " & _
  16.                 "FROM comparison; "
  17.  
  18.     rstarray.ActiveConnection = CurrentProject.Connection
  19.     rstarray.CursorType = adOpenDynamic
  20.     rstarray.LockType = adLockOptimistic
  21.  
  22.     'open table to be used in array
  23.     rstarray.Open strsqlarray
  24.  
  25.     'populate first column
  26.     For i = 1 To 176
  27.         rstarray.MoveFirst
  28.         singleunitsadd(i, 1) = rstarray!diff
  29.         rstarray.MoveNext
  30.         Debug.Print singleunitsadd(i, 1)
  31.     Next
  32.     rstarray.MoveFirst
  33.     'populate second column
  34.      For j = 1 To 176
  35.         singleunitsadd(j, 2) = rstarray!tractnumber
  36.         rstarray.MoveNext
  37.         Debug.Print singleunitsadd(j, 2)
  38.     Next
  39.     rstarray.Close
  40.     Set rstarray = Nothing
  41.  
  42.  
  43.  '2. Get recordset to be updated--to be looped as a function of l   
  44. strsql = "SELECT TOP " & singleunitsadd(k, 1) & " MAPBLKLOT, c1, RESUNITS, parcelcounter, " & _
  45.              "tractcounter, tractid " & _
  46.              "FROM luse01 " & _
  47.              "ORDER BY parcelcounter;"
  48.  
  49.     rst.ActiveConnection = CurrentProject.Connection
  50.     rst.CursorType = adOpenDynamic
  51.     rst.LockType = adLockOptimistic
  52.  
  53.     'iterate one time for each tract and assign a number to all parcels in tract
  54.  
  55.     For k = 1 To 176
  56.         rst.Open strsql
  57.         rst.MoveFirst
  58.         Do Until rst.EOF
  59.             'rst!parcelcounter = lngcounter
  60.             rst!c1 = 1
  61.             rst.Update
  62.             rst.MoveNext
  63.             Debug.Print rst!MAPBLKLOT & _
  64.             "; " & rst!tractcounter & "; " & rst!tractid & "; " & rst!c1
  65.             On Error GoTo ErrorHandler
  66.         rst.Close
  67.         Set rst = Nothing
  68.  
  69.         Loop
  70.     Next k
  71.  
  72.  
  73. ErrorHandlerExit:
  74.         Exit Sub
  75. ErrorHandler:
  76.         If Err = 3021 Then    ' no current record
  77.             Resume Next
  78.         Else
  79.             MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
  80.             Resume ErrorHandlerExit
  81.         End If
  82.  
  83. End Sub
Oct 10 '07 #1
Share this Question
Share on Google+
10 Replies


ADezii
Expert 5K+
P: 8,638
You are trying to use an Array (singleunitsadd(dim1, dim2)) within the context of an SQL Statement that is derived from a Table not listed in the FROM Clause. I don't even think that this approach is possible, but even if it were, the Subscript out of range Error would be generated because of singleunitsadd(k, 1). From what I can see, k is not initialized up to this point and thus would contain the value of 0. The problem is that Option Base 1 has been explicitly stated so the Array Element at singleunitsadd(0, 1) makes no sense. Hope this helps. BTW, this seems to be a very unorthodox way to populate a Multidimensional Array, it is customarily done within nested For...Next constructs.
Oct 10 '07 #2

P: 21
You are trying to use an Array (singleunitsadd(dim1, dim2)) within the context of an SQL Statement that is derived from a Table not listed in the FROM Clause. I don't even think that this approach is possible, but even if it were, the Subscript out of range Error would be generated because of singleunitsadd(k, 1). From what I can see, k is not initialized up to this point and thus would contain the value of 0. The problem is that Option Base 1 has been explicitly stated so the Array Element at singleunitsadd(0, 1) makes no sense. Hope this helps. BTW, this seems to be a very unorthodox way to populate a Multidimensional Array, it is customarily done within nested For...Next constructs.
I think the table you want a reference to is listed in line 16? At any rate, I eliminated the base 1 declaration and assumed 0 as the start, and the Subscript out of range Error is no more. Now I get "Error No. 91: Description; Object variable or With block variable not set". This does not come up as a runtime error, but rather as a text box without the debug option. Since I don't declare any objects, I don't see what the problem is. Any ideas?
Oct 10 '07 #3

P: 21
I think the table you want a reference to is listed in line 16? At any rate, I eliminated the base 1 declaration and assumed 0 as the start, and the Subscript out of range Error is no more. Now I get "Error No. 91: Description; Object variable or With block variable not set". This does not come up as a runtime error, but rather as a text box without the debug option. Since I don't declare any objects, I don't see what the problem is. Any ideas?
I think I found out the problem.. I was looping the definition of a recordset, each time creating a new recordset for a new class (as based on a variable in the table) and performing calculations on that class. However, I closed and set the recordset object to nothing, so when the loop ran the next time, I got"Error No. 91: Description; Object variable or With block variable not set". Now at the end of the loop I don't set the recordset object to nothing, BUT I do close it.(the reason I re-populate the recordset each each class loop rather than using a filter method is that I want to select only a random subset of each class; hence I pass a variable to the sql statement determing exactly how many top records to return. I don't think I can specify the number of rows with the filter method)
Oct 10 '07 #4

Jim Doherty
Expert 100+
P: 897
I think the table you want a reference to is listed in line 16? At any rate, I eliminated the base 1 declaration and assumed 0 as the start, and the Subscript out of range Error is no more. Now I get "Error No. 91: Description; Object variable or With block variable not set". This does not come up as a runtime error, but rather as a text box without the debug option. Since I don't declare any objects, I don't see what the problem is. Any ideas?


Conceptually I see you building the SQL string as you have done for the recordset in much the same was as we build any other string 'criteria' clauses of SQL statements are prime examples built on the fly.

You are setting the TOP predicate based on your array value of k.......... problem with that is you need to keep tabs on your subscript range boundaries as Dez points out that 'k' was uninitialised at the first run in which results in an incomplete predicate 'a top blank space' in your built string

You might might want to look at the efficiency of continually opening and closing recordsets in a loop set against the performance gains of raw SQL 'Update' statements performed as a single batch. This is merely my observation from a design perspective, it might benchmark its worth...particularly if you have large datasets?

As for you object variable problem look at your line 67 you are deinitialising its reference so it has nothing to work with on the next return in the FOR loop.

Other than I have your code working here with option base 1

Jim
Oct 10 '07 #5

P: 21
Conceptually I see you building the SQL string as you have done for the recordset in much the same was as we build any other string 'criteria' clauses of SQL statements are prime examples built on the fly.

You are setting the TOP predicate based on your array value of k.......... problem with that is you need to keep tabs on your subscript range boundaries as Dez points out that 'k' was uninitialised at the first run in which results in an incomplete predicate 'a top blank space' in your built string

You might might want to look at the efficiency of continually opening and closing recordsets in a loop set against the performance gains of raw SQL 'Update' statements performed as a single batch. This is merely my observation from a design perspective, it might benchmark its worth...particularly if you have large datasets?

As for you object variable problem look at your line 67 you are deinitialising its reference so it has nothing to work with on the next return in the FOR loop.

Other than I have your code working here with option base 1

Jim
Jim,

Efficiency is a concern, but I don't know any other way to select a subset using the TOP predicate besides re-creating the recordset. I don't think I can filter using TOP. Besides, if the recordset is defined from the onset, the traffic is limited to those x records, no?
Oct 10 '07 #6

ADezii
Expert 5K+
P: 8,638
Jim,

Efficiency is a concern, but I don't know any other way to select a subset using the TOP predicate besides re-creating the recordset. I don't think I can filter using TOP. Besides, if the recordset is defined from the onset, the traffic is limited to those x records, no?
Why can't you generate a Random Number to represent the number of Records to return from each Class?
Oct 10 '07 #7

Jim Doherty
Expert 100+
P: 897
Jim,

Efficiency is a concern, but I don't know any other way to select a subset using the TOP predicate besides re-creating the recordset. I don't think I can filter using TOP. Besides, if the recordset is defined from the onset, the traffic is limited to those x records, no?
Limited too yes, but in the scripts case the rst is being opened, traversed and closed presumably an arbitary test figure of 176 times. To SQL UPDATE on a WHERE clause seems to me to be a more efficient method?. I don't wish to sound purist or boringly critical as we all do things our own way anyway :))

Expand|Select|Wrap|Line Numbers
  1.  
  2. For k = 1 To 176
  3.         rst.Open strsql
  4.         rst.MoveFirst
  5.         Do Until rst.EOF
  6.             'rst!parcelcounter = lngcounter
  7.             rst!c1 = 1
  8.             rst.Update
  9.             rst.MoveNext
  10.             Debug.Print rst!MAPBLKLOT & _
  11.             "; " & rst!tractcounter & "; " & rst!tractid & "; " & rst!c1
  12.             On Error GoTo ErrorHandler
  13.         rst.Close
  14.         Loop
  15. Next k
  16.  
Regards

Jim
Oct 10 '07 #8

P: 21
Limited too yes, but in the scripts case the rst is being opened, traversed and closed presumably an arbitary test figure of 176 times. To SQL UPDATE on a WHERE clause seems to me to be a more efficient method?. I don't wish to sound purist or boringly critical as we all do things our own way anyway :))

Expand|Select|Wrap|Line Numbers
  1.  
  2. For k = 1 To 176
  3.         rst.Open strsql
  4.         rst.MoveFirst
  5.         Do Until rst.EOF
  6.             'rst!parcelcounter = lngcounter
  7.             rst!c1 = 1
  8.             rst.Update
  9.             rst.MoveNext
  10.             Debug.Print rst!MAPBLKLOT & _
  11.             "; " & rst!tractcounter & "; " & rst!tractid & "; " & rst!c1
  12.             On Error GoTo ErrorHandler
  13.         rst.Close
  14.         Loop
  15. Next k
  16.  
Regards

Jim
I would love to just run an update query, the reason that doesn't seem to work in this case is that the number of records to update in each class depends on a value in another table determining how large the subset of each class will be updated. Now I have the problem that I wrap the recordset definition into a for k = 1 to 175...next expression where the k is used twice in the SQL string: first to filter only a given subset of records (stored in an array in the early part of the function) and second, to filter using WHERE. The problem, then, is that I can see in the immediate window that it actually does cycle through the K's (see below, second to last column), but judging from the recordset returned, it is the same every time. I am at my wit's end here; the fact that I can pass the constant in the query seems to me a proof of concept, but the execution doesn't do it for me.

Expand|Select|Wrap|Line Numbers
  1. Sub cycletest()
  2.  
  3.     Dim rstarray As ADODB.Recordset
  4.     Set rstarray = New ADODB.Recordset
  5.     Dim rst As ADODB.Recordset
  6.     Set rst = New ADODB.Recordset
  7.     Dim strsqlarray As String
  8.     Dim strsql As String
  9.     Dim singleunitsadd(175)
  10.     Dim i As Integer
  11.     Dim j As Integer
  12.     Dim k As Integer
  13.  
  14.     '1. Populate array
  15.     strsqlarray = "SELECT comparison.tractnumber as tractnumber, comparison.d1 AS diff " & _
  16.                 "FROM comparison " & _
  17.                 "ORDER BY tractnumber; "
  18.     rstarray.ActiveConnection = CurrentProject.Connection
  19.     rstarray.CursorType = adOpenStatic
  20.     rstarray.LockType = adLockOptimistic
  21.  
  22.     'open table to be used in array
  23.     rstarray.Open strsqlarray
  24.     Debug.Print rstarray.RecordCount
  25.  
  26.     'i = j = k = 1
  27.    ' populate first column
  28.     For i = 0 To 175
  29.         'rstarray.MoveFirst makes it go back to the first number every time..BAD!!
  30.         singleunitsadd(i) = rstarray!diff
  31.         rstarray.MoveNext
  32.         'Debug.Print singleunitsadd(i)
  33.     Next
  34.    'rstarray.MoveFirst
  35.     'populate second column
  36.    '  For j = 0 To 175
  37.    '     singleunitsadd(j, 1) = rstarray!tractnumber
  38.    '     rstarray.MoveNext
  39.    '     Debug.Print singleunitsadd(j, 1)
  40.    ' Next
  41.     rstarray.Close
  42.     Set rstarray = Nothing
  43.  
  44.     '2. Get recordset to be updated--to be looped as a function of k
  45.     strsql = "SELECT TOP  " & singleunitsadd(k) & "  luse01.MAPBLKLOT, luse01.c1, luse01.RESUNITS, " & _
  46.              "luse01.parcelcounter, luse01.tractcounter, luse01.tractid " & _
  47.              "FROM luse01 " & _
  48.              "WHERE (((luse01.tractcounter)= " & singleunitsadd(k) & " )) " & _
  49.              "ORDER BY tractcounter, randomnumber;"
  50.  
  51.     rst.ActiveConnection = CurrentProject.Connection
  52.     rst.CursorType = adOpenStatic
  53.     rst.LockType = adLockOptimistic
  54.  
  55.     'iterate one time for each tract and assign a number to all parcels in tract
  56.  
  57.    'On Error GoTo ErrorHandler
  58.      For k = 0 To 175
  59.         rst.Open strsql
  60.         'rst.Filter = "tractcounter = " & k
  61.         Debug.Print rst.RecordCount; k; singleunitsadd(k)
  62.  
  63.  
  64.         Do Until rst.EOF
  65.             'rst.MoveFirst
  66.             rst!c1 = 1
  67.  
  68.             'rst.Update
  69.             Debug.Print rst!parcelcounter & "; " & rst!MAPBLKLOT & _
  70.             "; " & rst!tractid & ": " & rst!c1 & ": " & k & "; " & singleunitsadd(k)
  71.             rst.MoveNext
  72.         Loop
  73.         rst.Close
  74.         'Set rst = Nothing
  75.     Next k
  76.     'Debug.Print lngcounter&; " records processed"
  77.     rst.Close
  78.     Set rst = Nothing
  79.  
  80. End Sub
and the immediate window...the second to last number is the K integer I use in the for k=1 to 175 statement.
Expand|Select|Wrap|Line Numbers
  1.  176 
  2.  39  0  39 
  3. 107; ; 015500: 1: 0; 39
  4. 30; ; 015500: 1: 0; 39
  5. 35; ; 015500: 1: 0; 39
  6. 69; ; 015500: 1: 0; 39
  7. 99; ; 015500: 1: 0; 39
  8. (lines omitted) 
  9. 39  1  82 
  10. 107; ; 015500: 1: 1; 82
  11. 30; ; 015500: 1: 1; 82
  12. 35; ; 015500: 1: 1; 82
  13. 69; ; 015500: 1: 1; 82
  14. (lines omitted)
Oct 11 '07 #9

Jim Doherty
Expert 100+
P: 897
I would love to just run an update query, the reason that doesn't seem to work in this case is that the number of records to update in each class depends on a value in another table determining how large the subset of each class will be updated. Now I have the problem that I wrap the recordset definition into a for k = 1 to 175...next expression where the k is used twice in the SQL string: first to filter only a given subset of records (stored in an array in the early part of the function) and second, to filter using WHERE. The problem, then, is that I can see in the immediate window that it actually does cycle through the K's (see below, second to last column), but judging from the recordset returned, it is the same every time. I am at my wit's end here; the fact that I can pass the constant in the query seems to me a proof of concept, but the execution doesn't do it for me.

Expand|Select|Wrap|Line Numbers
  1. Sub cycletest()
  2.  
  3. Dim rstarray As ADODB.Recordset
  4. Set rstarray = New ADODB.Recordset
  5. Dim rst As ADODB.Recordset
  6. Set rst = New ADODB.Recordset
  7. Dim strsqlarray As String
  8. Dim strsql As String
  9. Dim singleunitsadd(175)
  10. Dim i As Integer
  11. Dim j As Integer
  12. Dim k As Integer
  13.  
  14. '1. Populate array
  15. strsqlarray = "SELECT comparison.tractnumber as tractnumber, comparison.d1 AS diff " & _
  16. "FROM comparison " & _
  17. "ORDER BY tractnumber; "
  18. rstarray.ActiveConnection = CurrentProject.Connection
  19. rstarray.CursorType = adOpenStatic
  20. rstarray.LockType = adLockOptimistic
  21.  
  22. 'open table to be used in array
  23. rstarray.Open strsqlarray
  24. Debug.Print rstarray.RecordCount
  25.  
  26. 'i = j = k = 1
  27. ' populate first column
  28. For i = 0 To 175
  29. 'rstarray.MoveFirst makes it go back to the first number every time..BAD!!
  30. singleunitsadd(i) = rstarray!diff
  31. rstarray.MoveNext
  32. 'Debug.Print singleunitsadd(i)
  33. Next
  34. 'rstarray.MoveFirst
  35. 'populate second column
  36. ' For j = 0 To 175
  37. ' singleunitsadd(j, 1) = rstarray!tractnumber
  38. ' rstarray.MoveNext
  39. ' Debug.Print singleunitsadd(j, 1)
  40. ' Next
  41. rstarray.Close
  42. Set rstarray = Nothing
  43.  
  44. '2. Get recordset to be updated--to be looped as a function of k
  45. strsql = "SELECT TOP " & singleunitsadd(k) & " luse01.MAPBLKLOT, luse01.c1, luse01.RESUNITS, " & _
  46. "luse01.parcelcounter, luse01.tractcounter, luse01.tractid " & _
  47. "FROM luse01 " & _
  48. "WHERE (((luse01.tractcounter)= " & singleunitsadd(k) & " )) " & _
  49. "ORDER BY tractcounter, randomnumber;"
  50.  
  51. rst.ActiveConnection = CurrentProject.Connection
  52. rst.CursorType = adOpenStatic
  53. rst.LockType = adLockOptimistic
  54.  
  55. 'iterate one time for each tract and assign a number to all parcels in tract
  56.  
  57. 'On Error GoTo ErrorHandler
  58. For k = 0 To 175
  59. rst.Open strsql
  60. 'rst.Filter = "tractcounter = " & k
  61. Debug.Print rst.RecordCount; k; singleunitsadd(k)
  62.  
  63.  
  64. Do Until rst.EOF
  65. 'rst.MoveFirst
  66. rst!c1 = 1
  67.  
  68. 'rst.Update
  69. Debug.Print rst!parcelcounter & "; " & rst!MAPBLKLOT & _
  70. "; " & rst!tractid & ": " & rst!c1 & ": " & k & "; " & singleunitsadd(k)
  71. rst.MoveNext
  72. Loop
  73. rst.Close
  74. 'Set rst = Nothing
  75. Next k
  76. 'Debug.Print lngcounter&; " records processed"
  77. rst.Close
  78. Set rst = Nothing
  79.  
  80. End Sub
and the immediate window...the second to last number is the K integer I use in the for k=1 to 175 statement.
Expand|Select|Wrap|Line Numbers
  1.  176 
  2. 39 0 39 
  3. 107; ; 015500: 1: 0; 39
  4. 30; ; 015500: 1: 0; 39
  5. 35; ; 015500: 1: 0; 39
  6. 69; ; 015500: 1: 0; 39
  7. 99; ; 015500: 1: 0; 39
  8. (lines omitted) 
  9. 39 1 82 
  10. 107; ; 015500: 1: 1; 82
  11. 30; ; 015500: 1: 1; 82
  12. 35; ; 015500: 1: 1; 82
  13. 69; ; 015500: 1: 1; 82
  14. (lines omitted)
Hi akselo,

Please check your PM,s

Jim :)
Oct 11 '07 #10

P: 21
I would love to just run an update query, the reason that doesn't seem to work in this case is that the number of records to update in each class depends on a value in another table determining how large the subset of each class will be updated. Now I have the problem that I wrap the recordset definition into a for k = 1 to 175...next expression where the k is used twice in the SQL string: first to filter only a given subset of records (stored in an array in the early part of the function) and second, to filter using WHERE. The problem, then, is that I can see in the immediate window that it actually does cycle through the K's (see below, second to last column), but judging from the recordset returned, it is the same every time. I am at my wit's end here; the fact that I can pass the constant in the query seems to me a proof of concept, but the execution doesn't do it for me.

Expand|Select|Wrap|Line Numbers
  1. Sub cycletest()
  2.  
  3.     Dim rstarray As ADODB.Recordset
  4.     Set rstarray = New ADODB.Recordset
  5.     Dim rst As ADODB.Recordset
  6.     Set rst = New ADODB.Recordset
  7.     Dim strsqlarray As String
  8.     Dim strsql As String
  9.     Dim singleunitsadd(175)
  10.     Dim i As Integer
  11.     Dim j As Integer
  12.     Dim k As Integer
  13.  
  14.     '1. Populate array
  15.     strsqlarray = "SELECT comparison.tractnumber as tractnumber, comparison.d1 AS diff " & _
  16.                 "FROM comparison " & _
  17.                 "ORDER BY tractnumber; "
  18.     rstarray.ActiveConnection = CurrentProject.Connection
  19.     rstarray.CursorType = adOpenStatic
  20.     rstarray.LockType = adLockOptimistic
  21.  
  22.     'open table to be used in array
  23.     rstarray.Open strsqlarray
  24.     Debug.Print rstarray.RecordCount
  25.  
  26.     'i = j = k = 1
  27.    ' populate first column
  28.     For i = 0 To 175
  29.         'rstarray.MoveFirst makes it go back to the first number every time..BAD!!
  30.         singleunitsadd(i) = rstarray!diff
  31.         rstarray.MoveNext
  32.         'Debug.Print singleunitsadd(i)
  33.     Next
  34.    'rstarray.MoveFirst
  35.     'populate second column
  36.    '  For j = 0 To 175
  37.    '     singleunitsadd(j, 1) = rstarray!tractnumber
  38.    '     rstarray.MoveNext
  39.    '     Debug.Print singleunitsadd(j, 1)
  40.    ' Next
  41.     rstarray.Close
  42.     Set rstarray = Nothing
  43.  
  44.     '2. Get recordset to be updated--to be looped as a function of k
  45.     strsql = "SELECT TOP  " & singleunitsadd(k) & "  luse01.MAPBLKLOT, luse01.c1, luse01.RESUNITS, " & _
  46.              "luse01.parcelcounter, luse01.tractcounter, luse01.tractid " & _
  47.              "FROM luse01 " & _
  48.              "WHERE (((luse01.tractcounter)= " & singleunitsadd(k) & " )) " & _
  49.              "ORDER BY tractcounter, randomnumber;"
  50.  
  51.     rst.ActiveConnection = CurrentProject.Connection
  52.     rst.CursorType = adOpenStatic
  53.     rst.LockType = adLockOptimistic
  54.  
  55.     'iterate one time for each tract and assign a number to all parcels in tract
  56.  
  57.    'On Error GoTo ErrorHandler
  58.      For k = 0 To 175
  59.         rst.Open strsql
  60.         'rst.Filter = "tractcounter = " & k
  61.         Debug.Print rst.RecordCount; k; singleunitsadd(k)
  62.  
  63.  
  64.         Do Until rst.EOF
  65.             'rst.MoveFirst
  66.             rst!c1 = 1
  67.  
  68.             'rst.Update
  69.             Debug.Print rst!parcelcounter & "; " & rst!MAPBLKLOT & _
  70.             "; " & rst!tractid & ": " & rst!c1 & ": " & k & "; " & singleunitsadd(k)
  71.             rst.MoveNext
  72.         Loop
  73.         rst.Close
  74.         'Set rst = Nothing
  75.     Next k
  76.     'Debug.Print lngcounter&; " records processed"
  77.     rst.Close
  78.     Set rst = Nothing
  79.  
  80. End Sub
and the immediate window...the second to last number is the K integer I use in the for k=1 to 175 statement.
Expand|Select|Wrap|Line Numbers
  1.  176 
  2.  39  0  39 
  3. 107; ; 015500: 1: 0; 39
  4. 30; ; 015500: 1: 0; 39
  5. 35; ; 015500: 1: 0; 39
  6. 69; ; 015500: 1: 0; 39
  7. 99; ; 015500: 1: 0; 39
  8. (lines omitted) 
  9. 39  1  82 
  10. 107; ; 015500: 1: 1; 82
  11. 30; ; 015500: 1: 1; 82
  12. 35; ; 015500: 1: 1; 82
  13. 69; ; 015500: 1: 1; 82
  14. (lines omitted)

Problem solved by moving strsql to after for K=1 to 176.
Oct 12 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.