Eliminate Dupes with VB 1

[Rolliee]

Here is a way to eliminate duplicate entries deleting the older ones and keeping the new. Place a button on the form. In this case the table has an autonumber ID as field(0) and six other text fields. For mixed type fields just make the savr array a variant.

Private Sub cmdDupesGone_Click()
Dim rs As DAO.Recordset, savr(10) As String
Dim i As Integer, BookLast As String, SQLstr As String
Dim okay As Boolean

SQLstr = "SELECT * FROM TABLE1 ORDER BY field1,field2,field3,field4,field5,field6 ;"

Set rs = CurrentDb.OpenRecordset(SQLstr)
rs.MoveLast
rs.MoveFirst

For i = 1 To 6

savr(i) = Nz(rs.Fields(i))
Next
okay = False
BookLast = rs.Bookmark
rs.MoveNext
Do While Not rs.EOF
For i = 1 To 6
If i = 1 Then
okay = (rs.Fields(i) = savr(i))
Else
okay = okay And (rs.Fields(i) = savr(i))
End If
Next
If okay Then
rs.Bookmark = BookLast
rs.Delete
rs.MoveNext
For i = 1 To 6
savr(i) = rs.Fields(i)
Next
okay = False
BookLast = rs.Bookmark
End If



rs.MoveNext
Loop
Me.Requery
rs.Close
Set rs = Nothing

End Sub