numbering rows

  • Thread starter Thread starter Florin
  • Start date Start date
F

Florin

I have a table with fields:

Nrcrt Field 2 Field 3
F 40
F 40
F 50
F 50
F 50

and I want fill field "Nrcrt" (numbering rows depending on Field3) :


Nrcrt Field 2 Field 3
1 F 40
2 F 40
1 F 50
2 F 50
3 F 50

How could I do that?
Thanks
 
I used a variable (varLast) to hold the Field3 value was from the previous
record If the current record is the same, then just increment the counter,
otherwise reset it to 1 and hold the new value. Create a new sub in a form,
and just call it by name. The code is untested so try it on a copy first.

Dim db as DAO.Database
Dim rst as DAO.Recordset
Dim intCount as Integer
Dim varLast as Variant

Set db = CurrentDb
Set rst = db.OpenRecordset( "SELECT Field3, Nrcrt FROM MyTable ORDER BY
Field3")

With rst
Do Until .EOF
If !Field3 = varLast Then
intCount = intCount + 1
Else
intCount = 1
varLast = !Field3
End If

.Edit
!Nrcrt = intCount
.Update
.MoveNext
Loop
End With

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
 
Back
Top