replacing movenext with a counter to move through file 1

[Frank72]

I'm trying to find duplicate names and addresses so as to find repeat customers.I am tyring to start at first record and then enetering an inner loop to compare this record to
the rest

using the field name form the table does not work with movenext because it also moves the outer loop variable thus records in the outer loop always match the inner, how can i make the value of the outer loop var remain the same until the inner loop has run??

my attempt:

strFirstName = CustTable![FirstName].Row(StatCount)
strSecondName = CustTable.Row(StatCount)! [Surname] ????

 

[jebry]

Hi!

If you need to find duplicate records, why not use a find duplicates query? Access has a wizard to help you put together what you need to do. Once you create the query, you can call it in code using DoCmd.Openquery or by opening it as a recordset. Doing this in code is terribly inefficient and subject to error.

hth Jeff Bridgham
bridgham@purdue.edu

 

[THoey]

If you are going to do this in code, you need to create two recordsets:

Public Function CheckNames()
Dim db As Database
Dim rst1 As Recordset
Dim rst2 As Recordset

'/* Set Outer Recordset */
Set db = CurrentDb
Set rst1 = db.OpenRecordset("select MyName from tblMyTable order by MyName&quot
rst1.MoveFirst

Do While Not rst1.EOF

'/* Set Inner Recordset */
Set rst2 = db.OpenRecordset("select MyName from tblMyTable order by MyName&quot
rst2.MoveFirst

If rst1!MyName = rst2!MyName Then

' /* Do Something Here */

End If

rst2.MoveNext

If rst2.EOF Then
rst1.MoveNext
rst2.MoveFirst
End If
Loop

rst1.Close
rst2.Close

End Function

Now, I just hammered this out and DID NOT TEST IT!!! Please check before you run it...

Hope that helps...
Terry M. Hoey