Renumbering

[knightjp]

I made a table with one autonumber primary key and one rank.
I would like a way that when I select delete button on a form to delete a record, it will move the one below up and renumber the rank in ascending order...



I've attached a rough database table showing how the data is supposed to look

[Rod]

I think you forgot to attach your table.

No! Never change the primary key of a row.

Rank on the other hand is another matter. Assuming your rank is numeric, what you need to do is build a recordset in ascending rank order. Then using an incrementing counter go through the recordset row by row updating the rank to be that of the counter and incrementing the counter.

This may be somewhat inefficient if the number of rows is large and/or deletions are frequent. You could employ techniques to jump to the row immediately before the deletion and rerank from there on and/or delay the reranking until all deletions have occurred.

[knightjp]

Thanks Rod for the reply.
The truth of the matter is that when I take a report out, the numbering is left with gaps... my renumbering this was just so that the list numbers on the report would be left in sequence.
I found an easy of doing this without any coding, etc....

http://allenbrowne.com/casu-10.html

[Rod]

If you sourced from Allen Browne you could do no better!

[knightjp]

If you sourced from Allen Browne you could do no better!

Seriously?! The site is that good???

[TheShabz]

It's seriously that good. It stares at me from my bookmarks toolbar all the time.