How To Reset AutoNumber Values After Deleting A Value

Question

Hey am working in MS Access and my ID field is having autonumber field type but when i delete a field, the autonumber is also deleted and it is not reset.

How do i do it such that whenever i delete a record, the autonumbered ID isnt deleted and it is instead reset?

Answer 1

An AutoNumber field is intended to be a meaningless unique identifier. It shouldn't matter that there are "missing" values in the sequence after deleting records.

If you deleted the most recently created record(s), you can "reset" the AutoNumber field by selecting Database Tools > Compact and Repair Database. But if you delete older records, removing the gaps would mean changing the value of the AutoNmber field in existing records, and that would be a very bad idea.

Answer 2

As Hans said, the ONLY purpose of an Autonumber is to provide a unique identifier to a record.

This means that once a number is assigned it can never be assigned again. If the record is deleted that number is gone.

Is there a reason you think you need sequential numbering? There are ways to provide such. But it is rare that its needed.

Answer 3

If you really have a cogent need to number rows in an unbroken sequence than you should not use an autonumber, but compute the next number yourself with code.  You might like to take a look at CustomNumber.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have trouble downloading a specific file, clicking on the 'Download' button at the top of the page while no files are selected should download a zip file of all files in the folder, from which you should then be able to unpack the relevant file.

This little demo illustrates how to compute sequential numbers when rows are inserted into a table.  It also includes an illustration of how to reuse gaps in a sequence where rows have been deleted.

Another possible scenario is that you wish the sequence to be recalculated if a row is deleted, or if a new row is to be inserted within the sequence between two existing.  This is done in the following code, to recalculate a PaymentIndex column, which governs the order in which regular payments are made from an account each month:

Private Sub cmdReindex_Click()

    Dim rst As DAO.Recordset

    Dim strSQL As String

    Dim n As Integer

    Me.Dirty = False

    strSQL = "SELECT PaymentIndex FROM RegularPayments ORDER BY PaymentIndex"

    Set rst = CurrentDb.OpenRecordset(strSQL)

    With rst

        .MoveLast

        n = 1

        .MoveFirst

        Do While Not .EOF

            .Edit

            .Fields("PaymentIndex") = n

            .Update

            n = n + 1

            .MoveNext

        Loop

    End With

    Set rst = Nothing

    Me.Requery

End Sub

If a row has been deleted, executing the above code will renumber all existing rows following the deleted row, reducing the value by 1 in each case..  If a row is to be inserted, by using a single precision floating point number as the data type of the PaymentIndex column, a row can be given a value with a non-zero fractional element before executing the code.  If you want to insert a row between rows 8 and 9 say, you would give the new row a value of 8.5 before recalculating the sequence.  The new row would be given a value of 9 and in all following rows the current value would be incremented by 1.

Answer 4

Doh! Sorry Hans. That should have been directed to the OP, not you.

Answer 5

I understood that, Ken. No worries.