Sorting A recordset - searching for duplicates

[ridlt via AccessMonster.com]

I'm trying to search through a recordset for duplicates - But I must sort the
recordset first - But it errors on my sort statement. Any ideas?

Your response is greatly appreciated - RIDLT




Private Sub cmdAuditDups_Click()
'Find Dups
Dim strtblContract As String
Dim dtDate As Date
Dim strEmplast01 As String
Dim strPID As StringFormatEnum
Dim intSeqx As Integer

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblInputSurvey")

rst.Sort rst!tblContract, rst!Date, rst!EmpLast01, rst!PID, rst!Seqx
rst.MoveFirst

Do Until rst.EOF
strtblContract = rst!tblContract
dtDate = rst!Date
strEmplast01 = EmpLast01
strPID = PID
rst.MoveNext
If rst!tblContract = strtblContract And rst!Date = dtDate And rst!
EmpLast01 = strEmplast01 And rst!PID = strPID Then
rst.Edit
rst!dup = True
rst.Update
rst.MoveLast
rst.Edit
rst!dup = True
rst.Update
rst.MoveNext
End If
Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

 

[PieterLinden via AccessMonster.com]

Why not just open a sorted recordset?
Set rst = db.OpenRecordset(SELECT F1, F2, F3, F4 tblInputSurvey ORDER BY F2,
F3, F1")

 

[ridlt via AccessMonster.com]

Why not just open a sorted recordset?
Set rst = db.OpenRecordset(SELECT F1, F2, F3, F4 tblInputSurvey ORDER BY F2,
F3, F1")

Thanks PieterLinden - I will try this -

I'm new at VBA - Thanks agin for your help - RIDLT

 

[John W. Vinson]

I'm trying to search through a recordset for duplicates - But I must sort the
recordset first - But it errors on my sort statement. Any ideas?

sorting is NOT required to look for duplicates! Just look for duplicates
directly:

SELECT field1, field2, field3, Count(*)
FROM tablename
GROUP BY field1, field2, field3
HAVING Count(*) > 1;