Flagging Matches 1

[lizarde03]

I need to flag records that have the same name and phone number.

For example, Suzy Smith 123-456-789 & Suzy Smith 123-456-789 would need to be flagged PM1. The next match, john doe 456-789-123 & john doe 456-789-123 would need to be PM2 and so on. Anybody know how to do this?

 

[ByteMyzer]

The following sub should do what you need:

Public Sub tableFlag(fTable As String, fName As String, fPhone As String, fFlag As String)

    Dim rst As Recordset, fCounter As Long

    Set rst = CurrentDb.OpenRecordset("SELECT [" & fName & "], [" & fPhone _
        & "] FROM [" & fTable & "] GROUP BY [" & fName & "], [" & fPhone _
        & "] HAVING Count([" & fPhone & "])>1;", dbOpenSnapshot)

    Do While Not rst.EOF
        fCounter = fCounter + 1
        CurrentDb.Execute "UPDATE [" & fName & "] SET [" & fFlag & "]='PM" _
            & Format(fCounter, "#") & "' WHERE [" & fName & "]='" & rst.Fields(0) _
            & "' AND [" & fPhone & "]='" & rst.Fields(1) & "';"
        rst.MoveNext
    Loop

    rst.Close

End Sub

For example, for a table named MyTable, with text fields named Name, Number and Flag, call the sub thus:
[tt]Call tableFlag("MyTable", "Name", "Number", "Flag")[/tt]

See if this works for you.

 

[lizarde03]

Thanks ByteMyzer. Reading through this makes sense. However, I've never used code in access. Any tips?

 

[ByteMyzer]

Following these steps should get you on track:

1) Open up the database in Access.

2) Click on the Modules tab of the database window.

3) Insert a new Module (on the Menu bar, select Insert -> Module)

4) Copy the code listed above and paste it into the code window.

5) Save the module as mod_tableFlag.


Now, to call this sub, as in the above example I gave, for a table named MyTable, with text fields named Name, Number and Flag, go to the Debug window (press CTRL-G), type the following line and press Enter:
[tt]
Call tableFlag("MyTable", "Name", "Number", "Flag")
[/tt]

 

[lizarde03]

Thanks for the help. I got the module in there and changed my field names and the table name to match yours. I'm getting a runtime error (invalid argument) and it highlighted this portion.

Set rst = CurrentDb.OpenRecordset("SELECT [" & fName & "], [" & fPhone _
& "] FROM [" & fTable & "] GROUP BY [" & fName & "], [" & fPhone _
& "] HAVING Count([" & fPhone & "])>1;", dbOpenSnapshot)

 

[ByteMyzer]

Could be a missing object library reference. You might try this modified code:

Public Sub tableFlag(fTable As String, fName As String, fPhone As String, fFlag As String)

    Dim dbe, dbs, rst
    Dim fCounter As Long

    Set dbe = CreateObject("DAO.DBEngine")
    Set dbs = dbe.OpenDatabase(CurrentDb.NAME)
    Set rst = dbs.OpenRecordset("SELECT [" & fName & "], [" & fPhone _
        & "] FROM [" & fTable & "] GROUP BY [" & fName & "], [" & fPhone _
        & "] HAVING Count([" & fPhone & "])>1;", 4)

    Do While Not rst.EOF
        fCounter = fCounter + 1
        CurrentDb.Execute "UPDATE [" & fName & "] SET [" & fFlag & "]='PM" _
            & Format(fCounter, "#") & "' WHERE [" & fName & "]='" & rst.Fields(0) _
            & "' AND [" & fPhone & "]='" & rst.Fields(1) & "';"
        rst.MoveNext
    Loop

    rst.Close
    dbs.Close

    Set rst = Nothing
    Set dbs = Nothing
    Set dbe = Nothing

End Sub

 

[lizarde03]

Now i'm getting another runtime error saying that ActiveX component can't create the object. =(

 

[ByteMyzer]

In that case, try resuming the former code I supplied, then, while still in the code window, click on the menu bar: Tools -> References. Check to make sure that the Microsoft DAO Object Library is checked.