0

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

Shadow
  • 33,525
  • 10
  • 51
  • 64
Raptor-ZH
  • 17
  • 1
  • 1
  • 6
  • In a normalised environment, tijd would be stored in a separate table – Strawberry Oct 11 '17 at 10:03
  • 1
    show us examples of what you have tried to resolve this issue – Jacques Koekemoer Oct 11 '17 at 10:06
  • I'm not that familiar to acces so I googled it but haven't found a lot. What I tried: Making a query using UPDATE and making a query that assigns numbers after everyone was signed up. But both didn't work out as I hoped it did. – Raptor-ZH Oct 11 '17 at 10:11

1 Answers1

0

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.

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Thanks for your reply! As I said above, I'm not really that familiar to access... Can you be a bit more specific where and how I can implement this code? – Raptor-ZH Oct 11 '17 at 12:48
  • i found the accepted answer on this link ranks fairly adequately. Not sure what it will do with tie-breaks though. https://stackoverflow.com/questions/4463116/use-access-sql-to-do-a-grouped-ranking – Darren Bartrup-Cook Oct 11 '17 at 12:54
  • It is _code-behind_ of the form you use. Add a field named _Priority_ of data type Integer to your table, include it on the form, and insert the code as the _AfterUpdate_ event of the textbox bound to the field. – Gustav Oct 11 '17 at 13:06
  • @DarrenBartrup-Cook: The trick is to add the ID-Rank match to the collection once, then reading the rank is very speedy no matter how you browse the form. – Gustav Oct 11 '17 at 13:11
  • So @Gustav, when you are referring to the AfterUpdate, do you mean using it in a macro? At least that is what I get when I google this... – Raptor-ZH Oct 11 '17 at 18:53
  • No. It is code-behind, code belonging to the form. Look up a guide on "Beginning with Microsoft Access and VBA" or similar. – Gustav Oct 11 '17 at 20:10