Matching between a field in records

[azhar2006]

Good evening guys
Allow me to salute everyone and the organizers of this wonderful forum and the help they provide to others.
I have a form that is based on displaying information from a table. In this table there is a field named (StatFig) that carries a number for each person and this number must not match the number of another person. I divided the form into two parts, the first part displays all records, and the other part (List) displays people who are similar to the number I mentioned above, and I used the code below through a button. The problem is that this code only works in the case of hovering and moving the mouse pointer for each record, and then I have to press the button and wait for the result and so on. What I want is to click the button and it searches the records and shows the result in (List). That's it, thank you everyone

Sub FindDups()
    Me.List4.RowSource = ""

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim strOut As String
    strSql = "select * from  tblmastr where StatFig = " & Me.StatFig & " "
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)

    If rs.BOF And rs.EOF Then
        GoTo MyExit
    End If

    rs.MoveLast
    rs.MoveFirst

    If rs.RecordCount > 1 Then
        Do Until rs.EOF
            Me.List4.AddItem rs!ID & ";" & rs!StatFig & ";" & rs!FullName
            rs.MoveNext
        Loop
    Else
        MsgBox "no Duplicates"
    End If
MyExit:
    rs.Close
    Set rs = Nothing
    Set db = Nothing

End Sub

 

[theDBguy]

Hi. I haven't looked at your attachment yet, but it sounds like you could simply use a fomr/subform setup where you would link the related fields in the property settings.

 

[azhar2006]

Hi. I haven't looked at your attachment yet, but it sounds like you could simply use a fomr/subform setup where you would link the related fields in the property settings.

I did that and also kept moving the mouse pointer to one field after another until I found the same. This is like I open the table directly in the view and filter it ascending and here I will find the similar.

 

[theDBguy]

Not sue I understand what you mean. Does this help?

 

[bastanu]

Something like this maybe?

Cheers,

 

[azhar2006]

Not sue I understand what you mean. Does this help?

Thanks for the help dear theDBguy but the same problem I have to hover the mouse over all the records.

 

[azhar2006]

Something like this maybe?

Cheers,

Thank you very much, my friend bastanu , but I canceled the code and it became reliance on the query. Isn't this true?

 

[theDBguy]

Thanks for the help dear theDBguy but the same problem I have to hover the mouse over all the records.

Then please explain exactly what you need. I don't get it.

 

[bastanu]

Yes, I don't think you need code, the list box will show you all the duplicates.
Cheers,

 

[azhar2006]

Yes, I don't think you need code, the list box will show you all the duplicates.
Cheers,

I adopted your method because I could not find a solution to the above code. Your method is great, thank you my friend @bastanu I was hoping that the results were in (VBA) and not the queries

 

[bastanu]

You're welcome!

I think you want to see all dups regardless of what record is current on the form. Setting the row source to a query that gives you that is the easiest method to do that, but you can set that in VBA if you like Me.list4.RowSource="qryDups" or eve use your existing code by changing this line Set rs = db.OpenRecordset(strSql) to Set rs = db.OpenRecordset("qryDups")

Cheers,