Del / Mark duplicates

  • Thread starter Thread starter sbcglobal
  • Start date Start date
S

sbcglobal

I have some VB like below, to delete duplicate. The Access table is 'RED',
key index field is 'Index', the VB nevigate through all records in 'Index'
and delete duplicate if found.

There are 2 problems:

1. I have to sort key field 'Index' first before del, since the sub only
compare adjacent 2 recordsets. Is there any other way to do this? Or how
would I sort records in VB?

2. Instead deleting, I'd like to name another field called 'Duplicate'
(yes/no). Can I modify below VB to mark field 'Duplicate' to 'yes' or 'no',
to indicate this is duplicate record (or not)?

Note if there are 5 recordsets in 'Index' have value=12135, then I ONLY need
to mark the first one (in field 'Duplicate') 'No', and rest 4 records 'Yes'.

Many thanks,

**************************************************
The Spoon Does Not Exist.
+++++++++++++++++++++++++++++++++++++++++++++++++++
Private Sub del_duplicate()
On Error Resume Next

Dim db As Database
Dim rst As Recordset
Dim strDupName As String, strSaveName As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("RED")

If rst.BOF And rst.EOF Then
MsgBox "No records to process"
Else
rst.MoveFirst
Do Until rst.EOF
strDupName = rst.Fields("Index")
If strDupName = strSaveName Then
rst.Delete
Else
strSaveName = rst.Fields("Index")
End If
rst.MoveNext
Loop

Set rst = Nothing
Set db = Nothing

End If

End Sub

..
 
Back
Top