need help renaming duplicate URNS

[Guest]

Hi, i wonder if you can help,

i need to rename duplicate IDs to include an incrementing letter to make
them unique. the ids are alphanumeric, in the format: #A000#####. eg.
7F00013245. There is always a letter second digit, and always three zeros
after it. For reasons beyond my control, i need it to be the 3rd digit (1st
zero) that is replaced with a letter e.g. 7FA0013245 and the 2nd duplicate
would be renamed to 7FB0013245 etc etc.

also, just to make it more tricky, i have been asked not to include the
letter F when changing the 1st zero into a letter.

i cant quite work out how to go about this, any ideas?

 

[Guest]

First I would add a new field name IDReplace and use DAO to create the new ID
in this field.

Here's the code I would use for the scenario you've described to create the
new ID in the new field IDReplace.

--------------------------------
Public Function IDReplace()
Dim rst As DAO.Recordset
Dim rstUpdte As DAO.Recordset
Dim sID As String
Dim rID As String
Dim i As Integer
Set rst = CurrentDb.OpenRecordset("Select * from table order by id")
With rst
rst.MoveLast
rst.MoveFirst
Do While Not rst.EOF
sID = !Id
Set rstUpdte = CurrentDb.OpenRecordset("Select * from table where id =
'" & sID & "' order by id")
With rstUpdte
rstUpdte.MoveLast
rstUpdte.MoveFirst
rID = !Id
If rstUpdte.RecordCount = 1 Then
rstUpdte.Edit
rstUpdte!IDReplace = rID
rstUpdte.Update
Else
i = 65 ' Use with Chr function to set alpha character
Do While Not rstUpdte.EOF
rstUpdte.Edit
rID = Left(!Id, 2) & Chr(i) & Right(!Id, 7)
rstUpdte!IDReplace = rID
rstUpdte.Update
If i = 69 Then 'increment counter by 2 after E otherwise
increment by 1
i = i + 2
Else
i = i + 1
End If
rstUpdte.MoveNext
Loop
End If
End With
rstUpdte.Close
rst.MoveNext
Loop
End With
rst.Close

End Function