Why isn't this query working???

ian_w

Registered User.
Local time
Today, 22:54
Joined
Jun 13, 2006
Messages
64
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 !

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
 
Move the drec.MoveNext just above the Loop statement.

You can go here and see other solutions to this sort of thing.

.
 
Move the drec.MoveNext just above the Loop statement.

You can go here and see other solutions to this sort of thing.

.

Thanks for that, sadly still have the problem though.

When I try and count the records that are found in the query (which will be greater than 1 if there are duplicates) it is always showing recordcount as 1 :confused:
 
All working now...

Added in

Code:
drec.MoveLast
drec.MoveFirst
rec = drec.Recordcount

:)
 

Users who are viewing this thread

Back
Top Bottom