Access: A query that resets the autonumbering

Question

My database is made for skiing competitions. The idea is that you can fill in the times people ski, and the databse automatically calculates what kind of medal you earned based on someone who set the time first, your gender and your age. I have made a form that makes it able to sign up and give all the results.

The only problem I'm having now is when signing someone up, it must be ordered on age. I did that, but now the autonumbering is all messed up.

What I want is that I can put all the names of the competitors in, and after that I want to have a query which I can choose that assigns all of the starting numbers. Even when the numbers are allready assigned I want to reset it and assign them again if someone joins in lately. I don't want to have to delete the auto numbering field and make it again because most of the time I won't be using it myself but other people will, so I want a simple push on the button that assigns it.

Thanks in advance!

Example how my database and a competition looks like

Answer 1

An Autonumber field is only for identifying records. No more no less.

What you need is a Priority (or Rank) field.

In your form where you display the records, run code like this for that field:

Private Sub Priority_AfterUpdate()

    Dim rst             As DAO.Recordset
    Dim lngId           As Long
    Dim lngPriorityNew  As Long
    Dim lngPriorityFix  As Long

    ' Save record.
    Me.Dirty = False

    ' Prepare form.
    DoCmd.Hourglass True
    Me.Repaint
    Me.Painting = False

    ' Current Id and priority.
    lngId = Me!Id.Value
    lngPriorityFix = Nz(Me!Priority.Value, 0)
    If lngPriorityFix <= 0 Then
        lngPriorityFix = 1
        Me!Priority.Value = lngPriorityFix
        Me.Dirty = False
    End If

    ' Rebuild priority list.
    Set rst = Me.RecordsetClone
    rst.MoveFirst
    While rst.EOF = False
        If rst!Id.Value <> lngId Then
            lngPriorityNew = lngPriorityNew + 1
            If lngPriorityNew = lngPriorityFix Then
                ' Move this record to next lower priority.
                lngPriorityNew = lngPriorityNew + 1
            End If
            If Nz(rst!Priority.Value, 0) = lngPriorityNew Then
                ' Priority hasn't changed for this record.
            Else
                ' Assign new priority.
                rst.Edit
                    rst!Priority.Value = lngPriorityNew
                rst.Update
            End If
        End If
        rst.MoveNext
    Wend

    ' Reorder form and relocate record.
    Me.Requery
    Set rst = Me.RecordsetClone
    rst.FindFirst "Id = " & lngId & ""
    Me.Bookmark = rst.Bookmark

    ' Present form.
    Me.Painting = True
    DoCmd.Hourglass False

    Set rst = Nothing

End Sub

Just assign a rank to any record, and records will be renumbered as and if needed.