Find and show duplicate records

[Coolboy55]

I have a Parts table with a PartNumber field and a ModelNumber field. The PartNumber will be unique for each part, while there may be duplicates in the ModelNumber field.

I want the Parts form to show the PartNumbers of all parts with duplicate ModelNumbers in the SameParts textbox.

Here's what I have done so far, but I might be way off...

 
Private Sub Form_Current()
 
    Dim s_Matches As String
    Dim Rec As DAO.Recordset
 
    Set Rec = CurrentDb.OpenRecordset("SELECT [ModelNumber], [PartNumber] FROM Parts WHERE (((ModelNumber) In (SELECT [ModelNumber] FROM [Parts] As Tmp GROUP BY [ModelNumber] HAVING Count(*)>1 ))) ORDER BY PartNumber;")
 
    If s_Matches = Empty Then
 
        s_Matches = Rec!PartNumber
 
    Else
 
        s_Matches = s_Matches & ", " & Rec!PartNumber
 
    End If
 
    Me.SameParts = s_Matches
 
    s_Matches = Empty
    Set Rec = Nothing
 
End Sub
 
 

Can anyone help? :)

CB55

[cori25]

Hello...

You can do it this way although I think it would be much easier to create a "find duplicates query" and then have the Same Parts text box in your form pull in this data from the query you create. Essentially, this will accomplish what you are requesting.

Let me know if this approach helps.

[Coolboy55]

Hello...

You can do it this way although I think it would be much easier to create a "find duplicates query" and then have the Same Parts text box in your form pull in this data from the query you create. Essentially, this will accomplish what you are requesting.

Let me know if this approach helps.

Yes, you are right, this approach was far easier. Thanks!!