Inserting a Value in an Access Recordset while running a loop

This should be a piece of cake for Access developers.

I'm running a Do Loop (rs.EOF) where a table is set as the recordset.  The Table could include duplicate Id numbers across several records and in such case, I need to insert a value in the "Occurrence" field counting the occurrence.

For example:
Id          Occurrence
1                    1
2                    1
2                    2
2                    3
3                    1
SpaceCoastLifeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
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.
0
John TsioumprisSoftware & Systems EngineerCommented:
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..
0
PatHartmanCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SpaceCoastLifeAuthor Commented:
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.
0
Dale FyeCommented:
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

Open in new window

0
SpaceCoastLifeAuthor Commented:
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
0
Dale FyeCommented:
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
0
Fabrice LambertFabrice LambertCommented:
@Dale:
Line 15, you probably mean: rs.Edit ?
0
Dale FyeCommented:
good catch, Fabrice.

Yes, line 15 should read:

rs.Edit
0
SpaceCoastLifeAuthor Commented:
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
0
Fabrice LambertFabrice LambertCommented:
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, dbOpenDynaset)
    While Not rs.EOF
        If rs!Id <> lngLastID Then
            lngSeqNum = 0
            lngLastID = rs!Id
        End If
        rs.Edit
        lngSeqNum = lngSeqNum + 1
        rs!Occurrence = lngSeqNum
        rs.Update
        rs.MoveNext
    Wend
End Sub 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SpaceCoastLifeAuthor Commented:
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
0
Dale FyeCommented:
Change:
 rs!ID 

Open in new window

to:
rs.CustomerID

Open in new window

and then change:
rs!Occurrence

Open in new window

to:
rs!Order

Open 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.
0
Fabrice LambertFabrice LambertCommented:
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.
0
SpaceCoastLifeAuthor Commented:
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
0
Fabrice LambertFabrice LambertCommented:
you're updating the recordset before editing it. It can't work.
0
SpaceCoastLifeAuthor Commented:
So where does the Update belong? I moved it down trying before and after rsEdit and it still errors - albeit, a different error.
0
SpaceCoastLifeAuthor Commented:
Never mind. I moved rs.Edit to the top of the loop and it works fine now.
0
SpaceCoastLifeAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.