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
*
