Here is code I use. You will need a field MARK (string 1) to mark duplicates.
The code will scan the query get the 1st item, then mark the duplicates.
Then you can either delete the dupes , or only show the unmarked recs.
Code:
Public Sub RemoveDuplicates()
Dim db As Database
Dim rst 'As Recordset
Dim qdf As QueryDef
Dim vCurrDup, vPrevDup, vKey, vCurrFld, vAddr
DoCmd.SetWarnings False
pvQry = "qsSortedList"
pvDupeFld = "expr1"
pvChgFld = "mark"
Set db = CurrentDb
Set qdf = db.QueryDefs(pvQry)
Set rst = qdf.OpenRecordset(dbOpenDynaset)
vPrevDup = "*&%"
With rst
While Not .EOF
vCurrDup = .Fields(pvDupeFld) & ""
If vCurrDup <> "" Then
'-----------------------
'MARK THE DUPES...
'-----------------------
If vPrevDup = vCurrDup Then 'mark it if dupe
.Edit
.Fields(pvChgFld) = "X"
.Update
End If
End If
vPrevDup = vCurrDup
.MoveNext
Wend
End With
'===== FINSHED =========
'show non marked records
DoCmd.OpenQuery "qsShowUnmarked"
'OR
'delete marked records
sSql = "Delete * from table where [" & pvChgFld & "]='X'"
'DoCmd.RunSQL sSql
DoCmd.SetWarnings True
Set qdf = Nothing
Set rst = Nothing
Set db = Nothing
Exit Sub
ErrRemove:
MsgBox Err.Description, , "RemoveDuplicates():" & Err
End Sub