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!!