Someone please help.......

[MadMax7]

I have been sent a table that holds all our call details by day

i need to be a ble to check each record and if the next record in the table is the same then i need to update a field in my table [DuplicateFlag] with the word "duplicate" i have come up with the following that i seems to work except when updating the field it updates duplicate flag for all records in my table and not just the matching record

hope some can help

Private Sub Command0_Click()

Dim rst As Recordset
Dim strCLI As String


Set db = CurrentDb()
Set rst = db.openrecordset("tbl08006007008thmarch", dbopendynaset)
rst.MoveFirst
Do While Not rst.EOF
strCLI = rst!CLi
rst.MoveNext
If strCLI = rst!CLi Then
DoCmd.RunSQL "UPDATE tbl08006007008thmarch " & _
"SET tbl08006007008thmarch.DuplicateFlag= '" & "Duplicate" & "'"
Else
End If
Loop
rst.Close
End Sub

 

[Golom]

Since you're already scrolling through the recordset, just update it

Private Sub Command0_Click()

    Dim rst                         As DAO.Recordset
    Dim strCLI                      As String

    Set rst = CurrentDb().OpenRecordset("tbl08006007008thmarch", dbOpenDynaset)
    With rst
        Do While Not .EOF
            If strCLI <> !CLi Then
                strCLI = !CLi
            Else
                .Edit
                ![DuplicateFlag] = "Duplicate"
                .Update
            End If
            .MoveNext
        Loop
        .Close
    End With
End Sub

 

[Golom]

Oh ... by the way. Relational databases do not guarantee that records will be returned in any particular order if you don't use an ORDER BY clause. Since this code depends on records being ordered, perhaps you want

Set rst = CurrentDb().OpenRecordset ( _ 
          "Select * From tbl08006007008thmarch Order By CLi", _
          dbOpenDynaset)

 

[MajP]

Seems that it would be more efficient to just write a find duplicate / update query to do this.

 

[MadMax7]

Golom thank you for your help i was wondering if it would be possible to put a record count after the duplicate flag, so if there are 6 CLI records the same the duplicate flag would say duplicate1 for the first record all the way to duplicate6 then when there is a new duplicate CLI the count would re-start again from 1

hope this makes sense

 

[PHV]

Private Sub Command0_Click()
    Dim lngCount                    As Long
    Dim rst                         As DAO.Recordset
    Dim strCLI                      As String

    Set rst = CurrentDb().OpenRecordset("tbl08006007008thmarch", dbOpenDynaset)
    With rst
        Do While Not .EOF
            If strCLI <> !CLi Then
                strCLI = !CLi
                lngCount = 0
            Else
                .Edit
                lngCount = lngCount + 1
                ![DuplicateFlag] = "Duplicate" & lngCount
                .Update
            End If
            .MoveNext
        Loop
        .Close
    End With
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MadMax7]

that works great thanks