Looping a recordset definition based on number from array

[akselo]

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?

Sub cycletest()
 
    Dim rstarray As ADODB.Recordset
    Set rstarray = New ADODB.Recordset
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    Dim strsqlarray As String
    Dim strsql As String
    Dim singleunitsadd(176, 2)
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
 
     '1. Populate array
    strsqlarray = "SELECT comparison.tractnumber as tractnumber, comparison.d1 AS diff " & _
                "FROM comparison; "
 
    rstarray.ActiveConnection = CurrentProject.Connection
    rstarray.CursorType = adOpenDynamic
    rstarray.LockType = adLockOptimistic
 
    'open table to be used in array
    rstarray.Open strsqlarray
 
    'populate first column
    For i = 1 To 176
        rstarray.MoveFirst
        singleunitsadd(i, 1) = rstarray!diff
        rstarray.MoveNext
        Debug.Print singleunitsadd(i, 1)
    Next
    rstarray.MoveFirst
    'populate second column
     For j = 1 To 176
        singleunitsadd(j, 2) = rstarray!tractnumber
        rstarray.MoveNext
        Debug.Print singleunitsadd(j, 2)
    Next
    rstarray.Close
    Set rstarray = Nothing
 
 
  '2. Get recordset to be updated--to be looped as a function of l   
strsql = "SELECT TOP " & singleunitsadd(k, 1) & " MAPBLKLOT, c1, RESUNITS, parcelcounter, " & _
             "tractcounter, tractid " & _
             "FROM luse01 " & _
             "ORDER BY parcelcounter;"
 
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenDynamic
    rst.LockType = adLockOptimistic
 
    'iterate one time for each tract and assign a number to all parcels in tract
 
    For k = 1 To 176
        rst.Open strsql
        rst.MoveFirst
        Do Until rst.EOF
            'rst!parcelcounter = lngcounter
            rst!c1 = 1
            rst.Update
            rst.MoveNext
            Debug.Print rst!MAPBLKLOT & _
            "; " & rst!tractcounter & "; " & rst!tractid & "; " & rst!c1
            On Error GoTo ErrorHandler
        rst.Close
        Set rst = Nothing
 
        Loop
    Next k
 
 
ErrorHandlerExit:
        Exit Sub
ErrorHandler:
        If Err = 3021 Then    ' no current record
            Resume Next
        Else
            MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
            Resume ErrorHandlerExit
        End If
 
End Sub

[ADezii]

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.

[akselo]

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?

[akselo]

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)

[Jim Doherty]

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

[akselo]

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?

[ADezii]

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?

[Jim Doherty]

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 :))

 
For k = 1 To 176
        rst.Open strsql
        rst.MoveFirst
        Do Until rst.EOF
            'rst!parcelcounter = lngcounter
            rst!c1 = 1
            rst.Update
            rst.MoveNext
            Debug.Print rst!MAPBLKLOT & _
            "; " & rst!tractcounter & "; " & rst!tractid & "; " & rst!c1
            On Error GoTo ErrorHandler
        rst.Close
        Loop
Next k
 

Regards

Jim

[akselo]

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 :))

 
For k = 1 To 176
        rst.Open strsql
        rst.MoveFirst
        Do Until rst.EOF
            'rst!parcelcounter = lngcounter
            rst!c1 = 1
            rst.Update
            rst.MoveNext
            Debug.Print rst!MAPBLKLOT & _
            "; " & rst!tractcounter & "; " & rst!tractid & "; " & rst!c1
            On Error GoTo ErrorHandler
        rst.Close
        Loop
Next k
 

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.

Sub cycletest()
 
    Dim rstarray As ADODB.Recordset
    Set rstarray = New ADODB.Recordset
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    Dim strsqlarray As String
    Dim strsql As String
    Dim singleunitsadd(175)
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
 
    '1. Populate array
    strsqlarray = "SELECT comparison.tractnumber as tractnumber, comparison.d1 AS diff " & _
                "FROM comparison " & _
                "ORDER BY tractnumber; "
    rstarray.ActiveConnection = CurrentProject.Connection
    rstarray.CursorType = adOpenStatic
    rstarray.LockType = adLockOptimistic
 
    'open table to be used in array
    rstarray.Open strsqlarray
    Debug.Print rstarray.RecordCount
 
    'i = j = k = 1
   ' populate first column
    For i = 0 To 175
        'rstarray.MoveFirst makes it go back to the first number every time..BAD!!
        singleunitsadd(i) = rstarray!diff
        rstarray.MoveNext
        'Debug.Print singleunitsadd(i)
    Next
   'rstarray.MoveFirst
    'populate second column
   '  For j = 0 To 175
   '     singleunitsadd(j, 1) = rstarray!tractnumber
   '     rstarray.MoveNext
   '     Debug.Print singleunitsadd(j, 1)
   ' Next
    rstarray.Close
    Set rstarray = Nothing
 
    '2. Get recordset to be updated--to be looped as a function of k
    strsql = "SELECT TOP  " & singleunitsadd(k) & "  luse01.MAPBLKLOT, luse01.c1, luse01.RESUNITS, " & _
             "luse01.parcelcounter, luse01.tractcounter, luse01.tractid " & _
             "FROM luse01 " & _
             "WHERE (((luse01.tractcounter)= " & singleunitsadd(k) & " )) " & _
             "ORDER BY tractcounter, randomnumber;"
 
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic
 
    'iterate one time for each tract and assign a number to all parcels in tract
 
   'On Error GoTo ErrorHandler
     For k = 0 To 175
        rst.Open strsql
        'rst.Filter = "tractcounter = " & k
        Debug.Print rst.RecordCount; k; singleunitsadd(k)
 
 
        Do Until rst.EOF
            'rst.MoveFirst
            rst!c1 = 1
 
            'rst.Update
            Debug.Print rst!parcelcounter & "; " & rst!MAPBLKLOT & _
            "; " & rst!tractid & ": " & rst!c1 & ": " & k & "; " & singleunitsadd(k)
            rst.MoveNext
        Loop
        rst.Close
        'Set rst = Nothing
    Next k
    'Debug.Print lngcounter&; " records processed"
    rst.Close
    Set rst = Nothing
 
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.

 176 
 39  0  39 
107; ; 015500: 1: 0; 39
30; ; 015500: 1: 0; 39
35; ; 015500: 1: 0; 39
69; ; 015500: 1: 0; 39
99; ; 015500: 1: 0; 39
(lines omitted) 
39  1  82 
107; ; 015500: 1: 1; 82
30; ; 015500: 1: 1; 82
35; ; 015500: 1: 1; 82
69; ; 015500: 1: 1; 82
(lines omitted)

[Jim Doherty]

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.

Sub cycletest()
 
Dim rstarray As ADODB.Recordset
Set rstarray = New ADODB.Recordset
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim strsqlarray As String
Dim strsql As String
Dim singleunitsadd(175)
Dim i As Integer
Dim j As Integer
Dim k As Integer
 
'1. Populate array
strsqlarray = "SELECT comparison.tractnumber as tractnumber, comparison.d1 AS diff " & _
"FROM comparison " & _
"ORDER BY tractnumber; "
rstarray.ActiveConnection = CurrentProject.Connection
rstarray.CursorType = adOpenStatic
rstarray.LockType = adLockOptimistic
 
'open table to be used in array
rstarray.Open strsqlarray
Debug.Print rstarray.RecordCount
 
'i = j = k = 1
' populate first column
For i = 0 To 175
'rstarray.MoveFirst makes it go back to the first number every time..BAD!!
singleunitsadd(i) = rstarray!diff
rstarray.MoveNext
'Debug.Print singleunitsadd(i)
Next
'rstarray.MoveFirst
'populate second column
' For j = 0 To 175
' singleunitsadd(j, 1) = rstarray!tractnumber
' rstarray.MoveNext
' Debug.Print singleunitsadd(j, 1)
' Next
rstarray.Close
Set rstarray = Nothing
 
'2. Get recordset to be updated--to be looped as a function of k
strsql = "SELECT TOP " & singleunitsadd(k) & " luse01.MAPBLKLOT, luse01.c1, luse01.RESUNITS, " & _
"luse01.parcelcounter, luse01.tractcounter, luse01.tractid " & _
"FROM luse01 " & _
"WHERE (((luse01.tractcounter)= " & singleunitsadd(k) & " )) " & _
"ORDER BY tractcounter, randomnumber;"
 
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
 
'iterate one time for each tract and assign a number to all parcels in tract
 
'On Error GoTo ErrorHandler
For k = 0 To 175
rst.Open strsql
'rst.Filter = "tractcounter = " & k
Debug.Print rst.RecordCount; k; singleunitsadd(k)
 
 
Do Until rst.EOF
'rst.MoveFirst
rst!c1 = 1
 
'rst.Update
Debug.Print rst!parcelcounter & "; " & rst!MAPBLKLOT & _
"; " & rst!tractid & ": " & rst!c1 & ": " & k & "; " & singleunitsadd(k)
rst.MoveNext
Loop
rst.Close
'Set rst = Nothing
Next k
'Debug.Print lngcounter&; " records processed"
rst.Close
Set rst = Nothing
 
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.

 176 
39 0 39 
107; ; 015500: 1: 0; 39
30; ; 015500: 1: 0; 39
35; ; 015500: 1: 0; 39
69; ; 015500: 1: 0; 39
99; ; 015500: 1: 0; 39
(lines omitted) 
39 1 82 
107; ; 015500: 1: 1; 82
30; ; 015500: 1: 1; 82
35; ; 015500: 1: 1; 82
69; ; 015500: 1: 1; 82
(lines omitted)

Hi akselo,

Please check your PM,s

Jim :)

[akselo]

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.

Sub cycletest()
 
    Dim rstarray As ADODB.Recordset
    Set rstarray = New ADODB.Recordset
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    Dim strsqlarray As String
    Dim strsql As String
    Dim singleunitsadd(175)
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
 
    '1. Populate array
    strsqlarray = "SELECT comparison.tractnumber as tractnumber, comparison.d1 AS diff " & _
                "FROM comparison " & _
                "ORDER BY tractnumber; "
    rstarray.ActiveConnection = CurrentProject.Connection
    rstarray.CursorType = adOpenStatic
    rstarray.LockType = adLockOptimistic
 
    'open table to be used in array
    rstarray.Open strsqlarray
    Debug.Print rstarray.RecordCount
 
    'i = j = k = 1
   ' populate first column
    For i = 0 To 175
        'rstarray.MoveFirst makes it go back to the first number every time..BAD!!
        singleunitsadd(i) = rstarray!diff
        rstarray.MoveNext
        'Debug.Print singleunitsadd(i)
    Next
   'rstarray.MoveFirst
    'populate second column
   '  For j = 0 To 175
   '     singleunitsadd(j, 1) = rstarray!tractnumber
   '     rstarray.MoveNext
   '     Debug.Print singleunitsadd(j, 1)
   ' Next
    rstarray.Close
    Set rstarray = Nothing
 
    '2. Get recordset to be updated--to be looped as a function of k
    strsql = "SELECT TOP  " & singleunitsadd(k) & "  luse01.MAPBLKLOT, luse01.c1, luse01.RESUNITS, " & _
             "luse01.parcelcounter, luse01.tractcounter, luse01.tractid " & _
             "FROM luse01 " & _
             "WHERE (((luse01.tractcounter)= " & singleunitsadd(k) & " )) " & _
             "ORDER BY tractcounter, randomnumber;"
 
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic
 
    'iterate one time for each tract and assign a number to all parcels in tract
 
   'On Error GoTo ErrorHandler
     For k = 0 To 175
        rst.Open strsql
        'rst.Filter = "tractcounter = " & k
        Debug.Print rst.RecordCount; k; singleunitsadd(k)
 
 
        Do Until rst.EOF
            'rst.MoveFirst
            rst!c1 = 1
 
            'rst.Update
            Debug.Print rst!parcelcounter & "; " & rst!MAPBLKLOT & _
            "; " & rst!tractid & ": " & rst!c1 & ": " & k & "; " & singleunitsadd(k)
            rst.MoveNext
        Loop
        rst.Close
        'Set rst = Nothing
    Next k
    'Debug.Print lngcounter&; " records processed"
    rst.Close
    Set rst = Nothing
 
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.

 176 
 39  0  39 
107; ; 015500: 1: 0; 39
30; ; 015500: 1: 0; 39
35; ; 015500: 1: 0; 39
69; ; 015500: 1: 0; 39
99; ; 015500: 1: 0; 39
(lines omitted) 
39  1  82 
107; ; 015500: 1: 1; 82
30; ; 015500: 1: 1; 82
35; ; 015500: 1: 1; 82
69; ; 015500: 1: 1; 82
(lines omitted)

Problem solved by moving strsql to after for K=1 to 176.