I have the below code running a one-time process to update 675 records. Every time it gets to 415 I get a run-time error violating the primary key rules. Why is this happening? I cannot understandy why my code would violate the primary key rules. I am aware that I am updating the records of a primary key, but the i = i+1 should never duplicate right?
Code:
Dim varMetricNum, varMetricName
Dim i As Integer
Dim fldSum As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim mySQL
' DoCmd.Hourglass True
Set db = CurrentDb
Set rs = db.OpenRecordset("tblMetrics_1", dbOpenDynaset)
rs.MoveFirst
i = 0
Do While Not rs.EOF
i = i + 1
varMetricNum = "MET-" & i
varMetricName = rs![Metric Name]
mySQL = "UPDATE tblMetrics_1 SET [Metric Number]='" & varMetricNum & "' WHERE [Metric Name]='" & varMetricName & "'"
CurrentDb.Execute mySQL, dbFailOnError
rs.MoveNext
Loop
' DoCmd.Hourglass False
MsgBox "Complete.", vbOKOnly