Trying to delete duplicates in table.

[EddyLLC]

I am trying to delete all duplicate records in a table. I tried the following code but every time I get to the first duplicate number it deletes the duplicate then ends the code. I know it must be a simple omition but I am cross eyed looking at this code. I'm sure someone can see what I am missing.
Thanks.

Function DelDups()
Dim strNumber As String
DoCmd.Hourglass True
Set db = CurrentDb()
Set rst = db.OpenRecordset("flkpNumbers", dbOpenDynaset)

Do Until rst.EOF
strNumber = rst("NumT")
Do Until rst.EOF
rst.FindNext "[NumT] = '" & strNumber & "'"
If Not rst.NoMatch Then
rst.Delete
MsgBox "You Have Deleted The Duplicate Number " & strNumber & " !", vbOKOnly + vbInformation, "Update Process"
Else
Exit Do
End If
Loop
rst.MoveNext
Loop
MsgBox "You Have Deleted All Duplicate Numbers!", vbOKOnly + vbInformation, "Update Process"
DoCmd.Hourglass False
End Function

 

[traingamer]

The first time you come to a record that has no matches, it loops to end of table then stops. See .bookmark method to hold your place before looping.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[rjoubert]

1. Create a copy of your table (structure only, not data)

2. In the table design of the copy, set your NumT field's indexed property to "Yes (No Duplicates)"

3. Create an append query that will append the records from your original table into your copy of the table

4. You will be presented with a warning stating that not all records could be appended...click the Yes button to continue anyways.

Your new (copy) table will then have unique records.

 

[EddyLLC]

Thanks for the help. I'm not sure I understand. Do you mean the first time it comes to a record that has a match? I ask because I step through the code and it will go through hundreds of non matches until it comes to a match then goes to end of table, I guess.

 

[legos]

Depending on how your table is structured, here is some code that i use to delete duplicate values.

Dim rst As Recordset
Dim rst2 As Recordset
Dim db As Database
Dim dupvalue As String
Dim num As Integer
Dim i As Single
Dim counter As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("duptable")
rst.MoveFirst
For i = 0 To rst.recordcount -1
dupvalue = rst!Fieldwdups
num = DCount("fieldwdups", "duptable", "fieldwdups = '" & dupvalue & "'")
For counter = 0 To num - 2
Set rst2 = db.OpenRecordset("SELECT * FROM duptable WHERE fieldwdups='" & dupvalue & "'")
rst2.Delete
Next counter
rst.MoveNext
Next i

Another option is to create a query that shows duplicates, but only shows the first duplicate, you can then delete all of the values in the query, which will delete 1 of every duplicate value found. If there are multiple duplicates you could do this a few times. I usually use the code portion above to remove the duplicates and then run the query to check that it worked.

Durible Outer Casing to Prevent Fall-Apart

 

[EddyLLC]

Thanks for the code legos. I can't get it to work. When I step through the code it only goes through once for the first record. I checked the recordcount in the immediate window and it tells me the count is 1 when in fact there are thousands.
No errors appear but it only loops thru the first record In the table then ends the function.

Thanks

 

[rjoubert]

Are you coding because you want to run this multiple times? If you only need to do this once, it may be easier to try my suggestion above.

 

[EddyLLC]

I don't know why I continued to fool around with this. I did try your suggestion and it worked great. Thanks for setting me straight with the help.

Eddy