recordset manipulation

I am searching through a recordset for duplicates, any duplicates I find I run a substring to remove the duplicate.

I need to run the same process again on the updated record set to remove any further duplicates

Here is the code i'm using


Private Sub duplicate_Click()
Dim myconnection As ADODB.Connection
Dim myrecordset As ADODB.Recordset
Dim anystring, mystring



Set myconnection = New ADODB.Connection
Set myrecordset = New ADODB.Recordset



myconnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\hanif batabase\e-mail DB.mdb"

myconnection.Open

If myconnection.State = adStateOpen Then



myrecordset.Open "SELECT dbo_stmgcode.int_email, dbo_stmbiogr.forename, dbo_stmbiogr.surname FROM dbo_stmbiogr INNER JOIN dbo_stmgcode ON dbo_stmbiogr.student_id = dbo_stmgcode.student_id WHERE (((dbo_stmgcode.int_email) In (SELECT [int_email] FROM [dbo_stmgcode] As Tmp GROUP BY [int_email] HAVING Count(*)>1 )))ORDER BY dbo_stmgcode.int_email", myconnection, adOpenDynamic, adLockOptimistic



Else
MsgBox "no Connection"
myconnection.Close
Exit Sub
End If

myrecordset.MoveFirst

On Error GoTo Transerror

myconnection.BeginTrans


Do Until myrecordset.EOF
anystring = Left(myrecordset!forename, 2) + Left(myrecordset!surname, 6) + "@roehmpaton.ac.uk"
myrecordset!int_email = anystring
myrecordset.Update

List1.AddItem (anystring)
myrecordset.MoveNext
Loop "I NEED TO LOOP THROUGH THIS RECORDSET AGIAN USING A DIFFERENT VALUE FOR ANYSTRING"

myconnection.CommitTrans
myrecordset.Close
myconnection.Close

Exit Sub

Transerror:
myconnection.RollbackTrans
myconnection.Close
myrecordset.Close
MsgBox Err.Description



End Sub


Thanxs

have you tried doing select distinct.

you also want
On Error GoTo Transerror

above the move first because if no records are returned, then an error will occur on movefirst.