Code Start for Record Update

VBABasix

Board Regular
Joined
Aug 15, 2005
Messages
52
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Chris, try changing this...
Code:
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...
Code:
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

Active Member
Joined
Nov 14, 2005
Messages
497
maybe I'm missunderstanding the problem but why not utilize the database to find your dupes? Access has a find duplicates query wizard.
 

VBABasix

Board Regular
Joined
Aug 15, 2005
Messages
52
Thanks for the help. I was actually able to do this through a query after re-designing the table structures.

Chris
 

Watch MrExcel Video

Forum statistics

Threads
1,119,268
Messages
5,577,102
Members
412,768
Latest member
klig
Top