Set a value sequentially

[Kenln]

I have a table with a PK which is a number (not AutoNumber) for each record.

The problem is that, for several reasons, they may not stay consecutively numbered. i.e. they may start
1
2
3
4
5
6
7
8
9

and end up

1
2
5
6
6.5
6.7
8
9


I know how to use DAO, record count to see how many records, but how do I set the numbers back to 1, 2, 3, etc..., retaining the new/current order.

Thank you,

 

[khawar]

Do you want to issue same number after some time in the same field

 

[Kenln]

I would like the records consecutively numbered starting with 1.

 

[khawar]

Dim i As Long
        i = 1
            Set dbC = CurrentDb()
            Set rst = dbC.OpenRecordset("select * from YourTable order by YourTable.SomeField")
                Do While Not rst.EOF
                rst.Edit
                rst!Field1 = i
                rst.Update
                rst.MoveNext
                i = i + 1
            Loop
    rst.Close

try this code using your own table and field names

 

[Kenln]

I had a problem with this
Line No
1
2
4
5
6
6.5
6.75
7
8
9

When it got to 6.5 it tried to set it to 7 which caused duplicates. Here is what I ended up with.

what do you think?

Dim dbC As DAO.Database
Dim Rst As DAO.Recordset

Dim i As Single
    i = 1
    Set dbC = CurrentDb()
    Set Rst = dbC.OpenRecordset("select Line_No from tbl_Order order by tbl_Order.Line_No")

    Do While Not Rst.EOF
        Rst.Edit
        Rst!Line_No = Rst!Line_No * -1
        Rst.Update
        Rst.MoveNext
    Loop

    Rst.MoveFirst

    Do While Not Rst.EOF
        Rst.Edit
        Rst!Line_No = i
        Rst.Update
        Rst.MoveNext
        i = i + 1
    Loop

    Rst.Close

Thanks,

 

[khawar]

Thats alright

 

[wazz]

ingenious.