Create my own AutoNumber Field with AbsolutePosition

[Pirre001]

I wanna create my own AutoNumber Field with AbsolutePosition function. So it always is in numeric order and no number been skipped.
I do like this, but it doesen't work:

Dim Db As Database
Dim rs As Recordset
Dim SQL, SQL1 As String
Set Db = CurrentDb
SQL = "SELECT * From Table1"
Set rs = Db.OpenRecordset(SQL)
 
With rs
 
        .MoveLast
        .MoveFirst
 
Do While Not .EOF
 
             SQL1 = "UPDATE Table1 SET Table1.[IDT]=" & "'" & .AbsolutePosition + 1 & "'"
             Db.Execute SQL1
            
.MoveNext
        Loop
 
        .Close
    
End With
 
 
Set Db = Nothing
Set rs = Nothing

I guess I missed that I should have any criteria somewhere, but I dont know how I should do.

[brucevde]

You need to add the Where clause. Always try to use the table's primary key (I am assuming its IDT).

SQL1 = "UPDATE Table1 SET Table1.[IDT]=" & "'" & .AbsolutePosition + 1 & "'"
 
SQL1 = SQL1  & " Where Table1.[IDT] = " & "'" & .Fields("IDT").Value & "'"

[Pirre001]

Thanks brucevde!

I have found another solution also:

Dim Db As Database
Dim rs As Recordset
Dim SQL As String
Set Db = CurrentDb
SQL = "SELECT * From Table1"
Set rs = Db.OpenRecordset(SQL)
 
With rs
       .MoveLast
       .MoveFirst
Do While Not .EOF
        .Edit
        !IDT = .AbsolutePosition + 1
        .Update
  .MoveNext
        Loop
  .Close
End With
 
Set Db = Nothing
Set rs = Nothing