MoveFirst not working in code to find duplicate records

[meadows43]

Hello,

I am trying to write a procedure that will loop through the records in a table and compare them on one field to check for duplicates. If they are duplicates, I need the value from the Quantity fields for the two adjacent records (the table is sorted) to be added and assigned to the first record. Then, I would like to have the code delete the second (duplicate) record. For some reason, when I invoke rst.MoveFirst, it doesn't go to the first record in the table (I checked this by doing an assignment statement to try to modify the Quantity field in the first record). Here's what I've got so far:

Private Sub CombineDups()
Dim prevRecVal As String
Dim thisRecVal As String
Dim Quantity As String


Dim dbs As Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Shopping List", dbOpenTable)
With rst
.MoveFirst
.Edit
End With

thisRecVal = rst![Ingredient Num]

Do Until rst.EOF
rst.MoveNext
prevRecVal = thisRecVal
thisRecVal = rst![Ingredient Num]
Quantity = rst!Quantity
If thisRecVal = prevRecVal Then
With rst
.MovePrevious
!Quantity = rst!Quantity + Quantity
.MoveNext
.Delete
.Update
End With
End If
Loop

With rst
.Update
.Close
End With
End Sub

I am stumped. Can anyone please help me out?
Thanks,
CM

 

[AndyP]

Could be because of the rst.MoveNext command you have after the loop starts. In effect you start at the first record with your initial statement
With rst
.MoveFirst
.Edit
End With

but you then tell it immediately to go to the next record by using the rst.MoveNext statement before testing the IF statement. Therefore it will never evaluate the first record. You will need to move rst.MoveNext to after the last "End If" before the Loop command.

Hope this helps.

 

[meadows43]

Error when trying to Update Recordset

Thanks, I made sure the records are sorted by a make table query, but now I get an error when I try to update the recordset. After executing the code, any duplicate records are deleted from the table, but the Quantity field in the table is not updated(for each set of duplicate records, I am trying to assign the sum of the Quantity fields to the first duplicate record's Quantity field). For example:

Date Ingredient Num Quantity
1/21/03 21 1
1/21/03 21 3

the code should store "4" in the first Quantity field and then delete the second record.

I have tried calling rst.Update at different places in the code, but have determined using the debugger that updating the recordset is what triggers the error. I know I declared the local variable Quantity as a String, but I changed it to Integer, so that's not the problem. Any suggestions, please?

 

[WayneRyan]

cm,

I haven't looked too hard at this, but you should probably
update the record before moving on.

.MovePrevious
!Quantity = rst!Quantity + Quantity
.Update ' New line of code
.MoveNext
.Delete

Wayne

 

[meadows43]

Thank you! I did not realize I needed to update the recordset before .MoveNext. Now it works.

CM