You really should fix some things before moving on:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not begin an object name with a number.
Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.
PK fields (if numeric) should be Long Integers <<<----
See Microsoft Access Tables: Primary Key Tips and Techniques
**** USE A COPY OF YOUR DB TO TEST THIS CODE****
I say again **** USE A COPY OF YOUR DB TO TEST THIS CODE****
Here is the code I came up with. Create a standard module ("Module1"??). Paste the code below into the module.
Click anywhere in the code and press the F5 key.
Code:
Option Compare Database '<<-- should be at the top of EVERY module
Option Explicit '<<-- should be at the top of EVERY module
Public Sub RemoveDuplicates()
Dim d As DAO.Database
Dim r As DAO.Recordset
Dim sSQL As String
Dim Commodity2Keep As Long
Dim Commodity2Delete As Long
Dim StTime As Date
Dim EndTime As Date
Dim knt As Long
StTime = Now()
Set d = CurrentDb
Set r = d.OpenRecordset("FindDuplicatesForTblCommodities")
If Not r.BOF And Not r.EOF Then
r.MoveLast
r.MoveFirst
knt = 0
Do
knt = knt + 1
'get first Commodity ID
Commodity2Delete = r!CommodityID
r.MoveNext
'get second Commodity ID
Commodity2Keep = r!CommodityID
'update TblConsignees
sSQL = "UPDATE TblConsignees"
sSQL = sSQL & " SET TblConsignees.CommodityID = " & Commodity2Keep
sSQL = sSQL & " WHERE TblConsignees.CommodityID = " & Commodity2Delete
' Debug.Print sSQL
d.Execute sSQL, dbFailOnError
'update TblShippers
sSQL = "UPDATE TblShippers"
sSQL = sSQL & " SET TblShippers.CommodityID = " & Commodity2Keep
sSQL = sSQL & " WHERE TblShippers.CommodityID = " & Commodity2Delete
' Debug.Print sSQL
d.Execute sSQL, dbFailOnError
'delete from TblCommodities
sSQL = "Delete *"
sSQL = sSQL & " FROM TblCommodities"
sSQL = sSQL & " WHERE TblCommodities.CommodityID = " & Commodity2Delete
' Debug.Print sSQL
d.Execute sSQL, dbFailOnError
r.MoveNext
Loop Until r.EOF
End If
'clean up
r.Close
Set r = Nothing
Set d = Nothing
EndTime = Now()
MsgBox "Done!" & " Elapsed time = " & DateDiff("s", StTime, EndTime) & " seconds for " & knt & " loops"
End Sub