How can I order a Set recIn = db.OpenRecordset("MatterEmails") (1 Viewer)

[atrium]

The code below doesn't work properly because I believe that the OpenRecodset("MatterEmails") is in it's native order and I need it recin!MatterId and then recin!EmailAddress order
Can anyone help me please

Dim db As DAO.Database
Dim recIn As DAO.Recordset
Dim strLastField1 As String
Dim strLastField2 As String
Dim strLastField3 As String
Dim lngRecordsDeleted As Long
Dim lngRecordsRead As Long
lngRecordsDeleted = 0
lngRecordsRead = 0
Set db = CurrentDb()
Set recIn = db.OpenRecordset("MatterEmails")
If recIn.EOF Then
    MsgBox ("No Input Records")
    recIn.Close
    Set recIn = Nothing
    Set db = Nothing
    Exit Sub
End If
Do
    If recIn!MatterId = strLastField1 And recIn!EmailAddress = strLastField2 Then
       recIn.Delete
       lngRecordsDeleted = lngRecordsDeleted + 1
    Else
        strLastField1 = recIn!MatterId
        strLastField2 = recIn!EmailAddress
        'strLastField3 = recIn!Field3
    End If
    lngRecordsRead = lngRecordsRead + 1
    recIn.MoveNext
Loop Until recIn.EOF
recIn.Close
Set recIn = Nothing
Set db = Nothing
MsgBox ("You Read " & lngRecordsRead & " Matter email records and Deleted " & lngRecordsDeleted & "  email records")

 

[theDBguy]

So, instead of simply using the name of the table, you can either create a sorted query and use its name or use an SQL statement with an ORDER BY clause.

 

[arnelgp]

the code you have is Deleting All records?
why not use:

db.Execute "Delete * From MatterEmails"
msgbox "You read " & db.RecordsAffected & " records and Deleted " & db.RecordsAffected & " records."

 

[atrium]

Thank you theDBguy I created a query and worked perfectly

Thanks once again

 

[theDBguy]

Thank you theDBguy I created a query and worked perfectly

Thanks once again

Hi. You're welcome. Glad to hear you got it to work. Good luck with your project.