SpaceCoastLife
asked on
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
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
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.
ASKER
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.
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
ASKER
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
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 ?
Line 15, you probably mean: rs.Edit ?
good catch, Fabrice.
Yes, line 15 should read:
rs.Edit
Yes, line 15 should read:
rs.Edit
ASKER
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.CustomerI d, tempEmerContacts.Order FROM tempEmerContacts ORDER BY tempEmerContacts.CustomerI d"
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
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.CustomerI
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.CustomerI d, tempEmerContacts.Order FROM tempEmerContacts ORDER BY tempEmerContacts.CustomerI d"
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
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.CustomerI
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:
But like we said before, if you don't have some other field, besides CustomerID to order by, then the sequence is probably irrelevant.
rs!ID
to:rs.CustomerID
and then change:rs!Occurrence
to:rs!Order
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.
For instance, the Id field don't exist in your query (but existed in Dale's query), substitute it with CustomerId.
ASKER
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.CustomerI d, tempEmerContacts.Order FROM tempEmerContacts ORDER BY tempEmerContacts.CustomerI d"
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
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.CustomerI
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.
ASKER
So where does the Update belong? I moved it down trying before and after rsEdit and it still errors - albeit, a different error.
ASKER
Never mind. I moved rs.Edit to the top of the loop and it works fine now.
ASKER
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!
Anyway, good job. Thanks!
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.