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

Automatic Iterating through filtered subsets of a recordset

P: 21
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.

Expand|Select|Wrap|Line Numbers
  1. Sub addcounter()
  2.     Dim intcounter As Integer
  3.     Dim rst As ADODB.Recordset
  4.     Set rst = New ADODB.Recordset
  5.     Dim strsql As String
  6.     Dim trackcounter As Integer
  7.     strsql = "SELECT MAPBLKLOT, RESUNITS, parcelcounter, " & _
  8.              "tractcounter " & _
  9.              "FROM luse01; "
  10.  
  11.     rst.ActiveConnection = CurrentProject.Connection
  12.     rst.CursorType = adOpenDynamic
  13.     rst.LockType = adLockOptimistic
  14.  
  15.     'select all parcels into recordset
  16.     rst.Open strsql
  17.  
  18.     'iterate one time for each census tract and assign a number to all
  19.     'parcels in tract
  20.     For intcounter = 1 To 176
  21.  
  22.         'filter so incrementing counter is assigned to parcels in one tract
  23.         ' at a time
  24.         'the rst.filter below seems to be the problem. Can I not pass a 
  25.         'variable here within the filter string?
  26.         rst.Filter = "tractcounter = intcounter" 
  27.         Do Until rst.EOF
  28.             intcounter = intcounter + 1
  29.             rst!parcelcounter = intcounter
  30.             rst.Update
  31.         Loop
  32.     Next intcounter
  33.     Debug.Print intcounter & " records processed"
  34.     rst.Close
  35.     Set rst = Nothing
  36.  
  37. End Sub
Oct 8 '07 #1
Share this Question
Share on Google+
5 Replies


FishVal
Expert 2.5K+
P: 2,653
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.

Expand|Select|Wrap|Line Numbers
  1. Public Function NumerateParcel(ByVal lngTractCounter As Integer) As Integer
  2.  
  3.     Static lngParcelCounter As Integer
  4.     Static lngCurrentTractCounter As Integer
  5.  
  6.     If lngCurrentTractCounter <> lngTractCounter Then
  7.         lngCurrentTractCounter = lngTractCounter
  8.         lngParcelCounter = 1
  9.     Else
  10.         lngParcelCounter = lngParcelCounter + 1
  11.     End If
  12.  
  13.     ParcelCounter = lngParcelCounter
  14.  
  15. End Function
  16.  
Expand|Select|Wrap|Line Numbers
  1. UPDATE (SELECT * FROM luse01 ORDER BY tractcounter) SET parcelcounter = NumerateParcel([tractcounter]);
  2.  
Oct 8 '07 #2

P: 21
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.

Expand|Select|Wrap|Line Numbers
  1. Public Function NumerateParcel(ByVal lngTractCounter As Integer) As Integer
  2.  
  3.     Static lngParcelCounter As Integer
  4.     Static lngCurrentTractCounter As Integer
  5.  
  6.     If lngCurrentTractCounter <> lngTractCounter Then
  7.         lngCurrentTractCounter = lngTractCounter
  8.         lngParcelCounter = 1
  9.     Else
  10.         lngParcelCounter = lngParcelCounter + 1
  11.     End If
  12.  
  13.     ParcelCounter = lngParcelCounter
  14.  
  15. End Function
  16.  
Expand|Select|Wrap|Line Numbers
  1. UPDATE (SELECT * FROM luse01 ORDER BY tractcounter) SET parcelcounter = NumerateParcel([tractcounter]);
  2.  
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 ??
Oct 8 '07 #3

FishVal
Expert 2.5K+
P: 2,653
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. :)

Expand|Select|Wrap|Line Numbers
  1. Public Function NumerateParcel(ByVal lngTractCounter As Integer) As Integer
  2.  
  3.     Static lngParcelCounter As Integer
  4.     Static lngCurrentTractCounter As Integer
  5.  
  6.     If lngCurrentTractCounter <> lngTractCounter Then
  7.         lngCurrentTractCounter = lngTractCounter
  8.         lngParcelCounter = 1
  9.     Else
  10.         lngParcelCounter = lngParcelCounter + 1
  11.     End If
  12.  
  13.     NumerateParcel = lngParcelCounter
  14.  
  15. End Function
  16.  
Oct 8 '07 #4

P: 21
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. :)

Expand|Select|Wrap|Line Numbers
  1. Public Function NumerateParcel(ByVal lngTractCounter As Integer) As Integer
  2.  
  3.     Static lngParcelCounter As Integer
  4.     Static lngCurrentTractCounter As Integer
  5.  
  6.     If lngCurrentTractCounter <> lngTractCounter Then
  7.         lngCurrentTractCounter = lngTractCounter
  8.         lngParcelCounter = 1
  9.     Else
  10.         lngParcelCounter = lngParcelCounter + 1
  11.     End If
  12.  
  13.     NumerateParcel = lngParcelCounter
  14.  
  15. End Function
  16.  
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?
Oct 8 '07 #5

FishVal
Expert 2.5K+
P: 2,653
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
Oct 9 '07 #6

Post your reply

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