Inserting a Value in an Access Recordset while running a loop

So, what is your question?

Instead of using the table as the source of the recordset, use a query:

SELECT ID, DateField, Occurrence
FROM yourtable
ORDER BY ID, DateField

Then, inside your loop, keep track of the previous ID value. If the ID value of the current record is different than the previous, then reset your variable for occurrence.  If the ID is the same, then increment the value of occurrence.  Finally, update the value of the Occurrence field.

But in order to do this, you must have some field that you can use to sort your ID values on, either a date or maybe an autonumber field.

Well you need to 2 recordsets... the one you have and one assisting one that will hold the ID and the counter of occurrences...on each iteration you search the 2nd recordset for the counter ...and you update both the recordsets..

This should be a piece of cake for Access developers.

True but most of us wouldn't store the information.  The reason being that the number you are generating is completely dependent on the sort order of the recordset.  If you sort the recordset differently, the assigned values should be different.

What is the point of doing this?  Would you expect the numbers to change if the sort order changes?  One thing to keep in mind is that when you are not sorting by a unique identifier, the sequence of individual records my change from one time to the next.  Equal values in a sort have no fixed order.

The field is sorted by  Id number always and is not dynamic. That is to say, this is a one time run - any yes, the recordset is a query I'm using, not a table.
I appreciate the concepts offered but I already understand the concepts. I was hoping for some code examples.

Dim db as dao.database
Dim rs as dao.recordset
Dim strsql as string
Dim lngLastID as long
Dim lngSeqNum as long 

set db = currentdb
strsql = "SELECT ID, DateField, Occurrence FROM yourtable ORDER BY ID, DateField"
set rs = db.openrecordset(strsql)
while not rs.eof
    if rs!ID <> lngLastID then
        lngSeqNum = 0
        lngLastID = rs!ID
    end if
    rs.update
    lngSeqNum = lngSeqNum + 1
    rs!Occurrence = lngSeqNum
    rs.Update
    rs.movenext
Next

Select allOpen in new window

Dale: Thanks. It errors out but I don't know if you were offering something that actually ran after I swapped in my stuff. Isn't it missing what lngLastID equals? Also rs!? Not familiar with that one

Then post the code that you translated my code into, so we can look at it.

lngLastID is initialized by the Dim statement as a long integer, which has a default value of zero.

The rs! syntax allows you to reference a field name, you can also use:

rs.fields("Occurrence") = lngSeqNum

@Dale:
Line 15, you probably mean: rs.Edit ?

good catch, Fabrice.

Yes, line 15 should read:

rs.Edit

Compile Error Next Without For

Private Sub cmdOrder_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strsql As String
Dim lngLastID As Long
Dim lngSeqNum As Long

Set db = CurrentDb
strsql = "SELECT tempEmerContacts.CustomerId, tempEmerContacts.Order FROM tempEmerContacts ORDER BY tempEmerContacts.CustomerId"
Set rs = db.OpenRecordset(strsql)
While Not rs.EOF
    If rs!Id <> lngLastID Then
        lngSeqNum = 0
        lngLastID = rs!Id
    End If
    rs.Update
    lngSeqNum = lngSeqNum + 1
    rs!Occurrence = lngSeqNum
    rs.Edit
    rs.MoveNext
Next
End Sub

Run-time error: 3265 Item Not Found in this Collection

Private Sub cmdOrder_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strsql As String
Dim lngLastID As Long
Dim lngSeqNum As Long

Set db = CurrentDb
strsql = "SELECT tempEmerContacts.CustomerId, tempEmerContacts.Order FROM tempEmerContacts ORDER BY tempEmerContacts.CustomerId"
Set rs = db.OpenRecordset(strsql)
While Not rs.EOF
    If rs!Id <> lngLastID Then        <---- ERROR
        lngSeqNum = 0
        lngLastID = rs!Id
    End If
    rs.Update
    lngSeqNum = lngSeqNum + 1
    rs!Occurrence = lngSeqNum
    rs.Edit
    rs.MoveNext
Wend
End Sub

Change:

 rs!ID 

Select allOpen in new window

to:

rs.CustomerID

Select allOpen in new window

and then change:

rs!Occurrence

Select allOpen in new window

to:

rs!Order

Select allOpen in new window

But like we said before, if you don't have some other field, besides CustomerID to order by, then the sequence is probably irrelevant.

Since you adapted your SQL query to feet your needs, you should also adapt field names retrieved from the recordset.
For instance, the Id field don't exist in your query (but existed in Dale's query), substitute it with CustomerId.

Run-time error 3020: Update or CancelUpdate Without AddNew or Edit

Private Sub cmdOrder_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strsql As String
Dim lngLastID As Long
Dim lngSeqNum As Long

Set db = CurrentDb
strsql = "SELECT tempEmerContacts.CustomerId, tempEmerContacts.Order FROM tempEmerContacts ORDER BY tempEmerContacts.CustomerId"
Set rs = db.OpenRecordset(strsql)
While Not rs.EOF
    If rs!CustomerId <> lngLastID Then
        lngSeqNum = 0
        lngLastID = rs!CustomerId
    End If
    rs.Update    <------ERROR
    lngSeqNum = lngSeqNum + 1
    rs!Order = lngSeqNum
    rs.Edit
    rs.MoveNext
Wend
End Sub

you're updating the recordset before editing it. It can't work.

So where does the Update belong? I moved it down trying before and after rsEdit and it still errors - albeit, a different error.

Never mind. I moved rs.Edit to the top of the loop and it works fine now.

Thanks for the help. I was going to throw some points to Fye also but couldn't figure out how to share. Seems every time I use EE the site has changed!

Anyway, good job. Thanks!