• Back to the Mailbag

How can I automatically renumber the records in an Access table when one is deleted?

"I have a table in which the records are ranked 1 to 20. When a record is deleted I want to have the others renumber automatically so there isn't a gap. Suppose I delete the record ranked 7, all the records after that will have to change. Record 8 becomes 7, 9 becomes 8, and so on. Is this a job for a query or does it need VBA?"

I think this might be possible with SQL but a simple VBA macro will do the trick. You need a number field that contains the rank numbers. It must not be an AutoNumber field (I almost always use an AutoNumber field for my Primary Key field) because you can't edit this type of field.

Here's how the table starts off. I have included an AutoNumber field called ID as the Primary Key field. This isn't important but it helps show what's going on. The Rank field is a Long Integer and contains the actual rankings of the records. The left-hand image below shows the table in it's original state, the right-hand image shows the table sorted in order of Rank:

>>>

Suppose we delete one of the records. When the record for Clare James, ranked 5, is deleted we have a gap in the rankings. We need to change James Ruane (currently ranked 6) to 5, Tamsin Graef (currently ranked 7) to 6 and so on to the end of the list.

>>>

Here's the code I came up with. It's written using DAO so a reference to DAO needs to be set if the code is to be used in Access 2000 and above.

Here's what the code does. It uses an SQL statement to open a recordset which consists of the data in the table (which is called tblRankings) sorted by the Rank field. I have chosen to open a sorted recordset rather than the table itself to make sure that the data is in the correct order. The recordset is opened as a dynaset because this allows me to edit the data.

The code uses an integer variable i as a counter with a starting value of 1. It moves to the first record and assigns the value of i to the Rank field, then it cranks up the value of i by 1. Then it loops through all the records, assigning the value of i to each one, then it closes the recordset. The result is a set of records which are consecutively numbered, in the same order as before, but with no gaps:

How do we get the code to run automatically when a record is deleted? A table doesn't have any events that we can attach the code to, but it can be done easily from a form. This form displays the records in their original order (i.e. in order of ID) but the code will work regardless of the sort order of the source data:

 Access forms have several events associated with deleting a record. The one most suitable in this instance is the AfterDelConfirm event which happens when the user clicks the Yes button on the warning message that is displayed when they ask to delete a record (by clicking the Delete Record toolbar button or choose Delete Record from the Edit menu). Here's the code:

I could have included the code from my UpdateRanking procedure here but I had written it in an Access module so it could be run from somewhere else if necessary. Instead I use the AfterDelConfirm event to "call" the UpdateRanking procedure. The If Statement makes sure that the code only runs if the user confirms the deletion, and I've included a message box to confirm that the rankings have been updated:

©2005 Martin Green. All rights reserved. martin@fontstuff.com