How to assign the available number in a sequence.

[Jmar1993]

I am using Access 2007.

I have a table which has a field called priority which may or may not have a number value in it. I want to be able to click on this record in a form have have the lowest available number assigned to it. I know how to assign the next number, but can't figure out how to assign the lowest available number. What I mean is that records may have been deleted after a number was assigned to them, so now there are gaps in the sequence such as 1,2,3,5. In this example I want VBA to assign this record I have clicked on the number 4 as it is the lowest available number. Can anyone help?

[zmbd]

Do you have a primary key?

[Rabbit]

Do a ranking query. Join the query to itself on the first row to the next row. Calculate the gap. Return the minimum number where the gap is greater than 1.