Matching between a field in records (1 Viewer)

azhar2006

Registered User.
Local time
Yesterday, 16:19
Joined
Feb 8, 2012
Messages
180
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 🌺
Code:
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
 

Attachments

  • ITD.accdb
    672 KB · Views: 210
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 16:19
Joined
Oct 29, 2018
Messages
18,791
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

Registered User.
Local time
Yesterday, 16:19
Joined
Feb 8, 2012
Messages
180
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

I’m here to help
Staff member
Local time
Yesterday, 16:19
Joined
Oct 29, 2018
Messages
18,791
Not sue I understand what you mean. Does this help?
 

Attachments

  • ITD (1).zip
    33.8 KB · Views: 221

bastanu

AWF VIP
Local time
Yesterday, 16:19
Joined
Apr 13, 2010
Messages
1,172
Something like this maybe?

Cheers,
 

Attachments

  • ITD.accdb
    484 KB · Views: 132

bastanu

AWF VIP
Local time
Yesterday, 16:19
Joined
Apr 13, 2010
Messages
1,172
Yes, I don't think you need code, the list box will show you all the duplicates.
Cheers,
 

azhar2006

Registered User.
Local time
Yesterday, 16:19
Joined
Feb 8, 2012
Messages
180
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

AWF VIP
Local time
Yesterday, 16:19
Joined
Apr 13, 2010
Messages
1,172
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,
 

Users who are viewing this thread

Top Bottom