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: