Code Start for Record Update

[VBABasix]

I am trying to loop through a table to identify records that have more than 1 customer number. And then update the Equip_id field in the table to a unique id. The first step that I was trying was simply opening the record set and checking for the match. Below if the code that I am using. The error message that I am getting is a type mismatch.

Private Sub Next_rec_Click()
Dim db As Database
Dim rs As Recordset
Dim ris As Long
Dim StrSQL As String

Set db = CurrentDb

StrSQL = "Select * from Td_Order;"

Set rs = db.OpenRecordset(StrSQL)

rs.MoveLast
rs.MoveFirst

Do While Not rst.EOF
rcdOne = rs!Sold_to
rs.MoveNext
rcdTwo = rs!Sold_to
If rcdOne = rcdTwo Then
MsgBox "I made it this far"

rst.MovePrevious
End If
Loop
End Sub

Would appreciate any help. Error occurs on the set rs line.

Thanks,

Chris

 

[SydneyGeek]

Chris, try changing this...

Private Sub Next_rec_Click()
Dim db As Database
Dim rs As Recordset
Dim ris As Long
Dim StrSQL As String

Set db = CurrentDb

StrSQL = "Select * from Td_Order;"

Set rs = db.OpenRecordset(StrSQL)

To this...

Private Sub Next_rec_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim ris As Long
Dim StrSQL As String

Set db = CurrentDb

StrSQL = "Select * from Td_Order;"

Set qdf = db.CreateQueryDef("",StrSQL)

Set rs = qdf.OpenRecordset

Denis

 

[nullZero]

maybe I'm missunderstanding the problem but why not utilize the database to find your dupes? Access has a find duplicates query wizard.

 

[VBABasix]

Thanks for the help. I was actually able to do this through a query after re-designing the table structures.

Chris