Automatic Iterating through filtered subsets of a recordset

[akselo]

Hi there good folks,

I am trying to populate a column in a table with a number incrementing for each class (as set by another field; 176 different classes in table of 150,000 records representing parcels in a city), and then when a new class is present, restart the numbering until the next class and so on. The point is that I ultimately want to randomly select and apply changes to fields within each class, which is why I need the incrementing numbers as a field now).

In the code below, the sql statement calls parcelcounter (the field to be populated) and tractcounter, which for each record identifies which tract/class (from 1 to 176) the parcel belongs to. The crux is that I want to iterate through 176 iterations, with each changing the filter so it looks at a new subset of the recordset and numbers all parcels within that subset starting at 1 in each class.

Sub addcounter()
    Dim intcounter As Integer
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    Dim strsql As String
    Dim trackcounter As Integer
    strsql = "SELECT MAPBLKLOT, RESUNITS, parcelcounter, " & _
             "tractcounter " & _
             "FROM luse01; "
 
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenDynamic
    rst.LockType = adLockOptimistic
 
    'select all parcels into recordset
    rst.Open strsql
 
    'iterate one time for each census tract and assign a number to all
    'parcels in tract
    For intcounter = 1 To 176
 
        'filter so incrementing counter is assigned to parcels in one tract
        ' at a time
        'the rst.filter below seems to be the problem. Can I not pass a 
        'variable here within the filter string?
        rst.Filter = "tractcounter = intcounter" 
        Do Until rst.EOF
            intcounter = intcounter + 1
            rst!parcelcounter = intcounter
            rst.Update
        Loop
    Next intcounter
    Debug.Print intcounter & " records processed"
    rst.Close
    Set rst = Nothing
 
End Sub

[FishVal]

Hi, askelo.

You are using the same variable as tractcounter and parcelcounter, but this has no influence as long as you don't generate filter expression properly (line #26). Suppose the results of code run are not the same you've been expecting.

BTW the same may be done using SQL with some VBA coding.

Public Function NumerateParcel(ByVal lngTractCounter As Integer) As Integer
 
    Static lngParcelCounter As Integer
    Static lngCurrentTractCounter As Integer
 
    If lngCurrentTractCounter <> lngTractCounter Then
        lngCurrentTractCounter = lngTractCounter
        lngParcelCounter = 1
    Else
        lngParcelCounter = lngParcelCounter + 1
    End If
 
    ParcelCounter = lngParcelCounter
 
End Function
 

UPDATE (SELECT * FROM luse01 ORDER BY tractcounter) SET parcelcounter = NumerateParcel([tractcounter]);
 

[akselo]

Hi, askelo.

You are using the same variable as tractcounter and parcelcounter, but this has no influence as long as you don't generate filter expression properly (line #26). Suppose the results of code run are not the same you've been expecting.

BTW the same may be done using SQL with some VBA coding.

Public Function NumerateParcel(ByVal lngTractCounter As Integer) As Integer
 
    Static lngParcelCounter As Integer
    Static lngCurrentTractCounter As Integer
 
    If lngCurrentTractCounter <> lngTractCounter Then
        lngCurrentTractCounter = lngTractCounter
        lngParcelCounter = 1
    Else
        lngParcelCounter = lngParcelCounter + 1
    End If
 
    ParcelCounter = lngParcelCounter
 
End Function
 

UPDATE (SELECT * FROM luse01 ORDER BY tractcounter) SET parcelcounter = NumerateParcel([tractcounter]);
 

How can you be so fast, and then on a holiday?? At any rate, the function does populate the field, but does so with 0 for all records/values of lngTractCounter ??

[FishVal]

How can you be so fast, and then on a holiday??

That isn't a problem if you know magic word. ;)

At any rate, the function does populate the field, but does so with 0 for all records/values of lngTractCounter ??

Aaaaa, just a so stupid mistake. Haven't changed function name when it returns value (last code line). Take a hot update. :)

Public Function NumerateParcel(ByVal lngTractCounter As Integer) As Integer
 
    Static lngParcelCounter As Integer
    Static lngCurrentTractCounter As Integer
 
    If lngCurrentTractCounter <> lngTractCounter Then
        lngCurrentTractCounter = lngTractCounter
        lngParcelCounter = 1
    Else
        lngParcelCounter = lngParcelCounter + 1
    End If
 
    NumerateParcel = lngParcelCounter
 
End Function
 

[akselo]

That isn't a problem if you know magic word. ;)



Aaaaa, just a so stupid mistake. Haven't changed function name when it returns value (last code line). Take a hot update. :)

Public Function NumerateParcel(ByVal lngTractCounter As Integer) As Integer
 
    Static lngParcelCounter As Integer
    Static lngCurrentTractCounter As Integer
 
    If lngCurrentTractCounter <> lngTractCounter Then
        lngCurrentTractCounter = lngTractCounter
        lngParcelCounter = 1
    Else
        lngParcelCounter = lngParcelCounter + 1
    End If
 
    NumerateParcel = lngParcelCounter
 
End Function
 

Well I should have seen that mistake had I paid attention. Now we know that I don't ;) With that fix, the run worked like a charm. THANKS!! For future reference, can you not pass an argument to recordset.filter as I tried in my first effort? Is that passed strictly as sql within the quotes?

[FishVal]

Well I should have seen that mistake had I paid attention. Now we know that I don't ;) With that fix, the run worked like a charm. THANKS!!

You are welcome. Hope you've got an idea rather than just copypasted code.

For future reference, can you not pass an argument to recordset.filter as I tried in my first effort? Is that passed strictly as sql within the quotes?

The value you pass to Recordset via Filter property has to be constant. Recordset object code has no idea whether intcounter is variable and what its value is.
So, instead
rst.Filter = "tractcounter = intcounter"
this
rst.Filter = "tractcounter = " & intcounter