Got a bit of a funny one here, got a table with a load of Company names in and it contains a number of duplicates, i've written this little sub to open the table, find duplicates, if it does then delete them until the record count = 1.
Its not doing what I want though, when I run my SELECT statement the record count is always coming back as 1 even though I know there are more??
I must be missing something simple !
Its not doing what I want though, when I run my SELECT statement the record count is always coming back as 1 even though I know there are more??
I must be missing something simple !
Code:
Sub Delete_Duplicate_Names()
Dim db As Database
Dim nrec As Recordset
Dim drec As Recordset
Dim a As Integer
Dim count As Long
Dim qstr As String
Dim Name As String
Dim rec As Long
Set db = CurrentDb
Set nrec = db.OpenRecordset("Normalised_Names", dbOpenSnapshot)
count = 0
With nrec
Do While Not .EOF
Name = nrec!NN_old_name
qstr = "SELECT * FROM Normalised_Names WHERE NN_Old_Name = '" & Name & "'"
Set drec = db.OpenRecordset(qstr, dbOpenDynaset)
a = 0
If drec.RecordCount > 1 Then
Do
drec.Edit
drec.Delete
drec.Update
a = a + 1
count = count + 1
Loop Until a = drec.RecordCount - 1
drec.MoveNext
End If
.MoveNext
Loop
End With
MsgBox count & " " & "records deleted."
End Sub