Incremental Number Based on Criteria

  • Thread starter Thread starter teddyb777
  • Start date Start date
T

teddyb777

In Column A I have an ID #.
Column A Column B
1 1
1 2
2 1
2 2
2 3
3 1
3 2
4 1

Any help would be appreciated.

I would like to add a column that would include an incremental number that
would in essence be counting up the number of times that the ID # appears in
the file.

Column A
1
1
2
2
2
3
3
4
 
loop through Recordset
~~~


Hi Teddy,

you can look through the recordset and fill these values

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub LoopThroughRecordset()

'Crystal
'strive4peace

'NEED reference to
'Microsoft DAO Library

'Set up error handler
On Error GoTo Proc_Err

'dimension variables

Dim r As DAO.Recordset

Dim i as integer _
, mKeyValue_last as Long

'Set up error handler
On Error GoTo Proc_Err

'open the Recordset
Set r = CurrentDb.OpenRecordset("Tablename", dbOpenDynaset)

'move to the first record
r.MoveFirst

mKeyValue_last = -99
i = 0

'loop through the Recordset until the end
Do While Not r.EOF

if r!fieldname_key <> mKeyValue_last then
mKeyValue_last = r!fieldname_key
i = 0
end if

'prepare record for editing
r.edit

r!fieldname_counter = i

'write changes to record
r.update

'go to next record
r.MoveNext
Loop

Proc_Exit:

if not r is nothing then
'close the recordset
r.close

'release object variables
Set r = Nothing
end if

exit sub

Proc_Err:
msgbox err.description _
,,"ERROR " & err.number _
& " LoopThroughRecordset"

Resume Proc_Exit

'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume


End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

WHERE
fieldname_counter is the fieldname for the counter data
fieldname_key is the fieldname for your primary key
fieldname_key is a long integer (autonumber) data type

once the values are filled, you can use the form BeforeUpdate or
BeforeInsert event to fill for new records

if you want to sort the table when you open it, you can use a query or
SQL statement in the OpenRecordset statement


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*
 
Back
Top