recordset manipulation

[hanif musa]

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

[Bill Crawley]

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.