[AccessD] Custom autonumbering

David, I always use an AutoNumber as my PKID, and not my generated number.
My original posting was:
/*
Using Autonumber for surrogate primary keys, I often need to manage custom
autonumbering for alternate unique keys, e.g. for order numbers.
I know this subject has been discussed several times before, but I couldn't
find exactly what I was looking for in the archive.
How do you manage custom autonumbering?

I do it this way, which may not be the most efficient, so I'm calling for
other suggestions:
1. On the form I set the property DefaultValue at design-time for the
control holding the custom autonumbering field to:
=DMAX(<field>,<table>) + 1
This will do in single-user environments, but will invite duplicate values
in multi-user environments.
So I use this run-time code in addition to the design-time DefaultValue:
2. Assuming the custom autonumbering field is called OrderNumber in a table
called tblOrders:
Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim intOrderNumber_Default As Integer
   Dim intOrderNumber_RunTime As Integer
   If NewRecord Then
      intOrderNumber_Default = OrderNumber.Value
      intOrderNumber_Runtine = DMAX("OrderNumber","tblOrders") + 1
      If intOrderNumber_Runtime <> intOrderNumber_Default Then
         OrderNumber.Value = intOrderNumber_Runtime
         MsgBox "Another user has created a new order with the number " & _
                intOrderNumber_Default & vbNewline & _
                "Your order has got the number " & intOrderNumber_Runtime
      End If
   End If
End Sub
Do you think this solution has any pitfalls, or do you just have another
more efficient/intelligent custom autonumbering?
/*

Your suggestion using a second table equals Drew's proposal, but it doesn't
satisfy my requirement of a customisable alternate key, on which I was not
enough explicit in my first posting.

Asger


-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af David McAfee
Sendt: 2. juli 2009 01:28
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Custom autonumbering

Asger, just make sure to use an autonumber as your PKID, not your generated
number.

If at some later point in life, the criteria changes for the number, so be
it.
You PKs & FKs will love you for it.

You can create a table for the sequence number by inserting a value into a
record with only an Autonumber PK and some other field.

I had a request where the facility request numbers were the date and a
sequence of 3 digits ( 20090701001-20090701999)

I created a sequence table and have a job that truncated that table each
night at midnight.

You need to know what happens if a number is in process of being created and
a second user
creates a new number but the first user/process cancels.

Will you leave a hole?

Will you save the record and flag it as cancelled?

What if user 2 fnishes before user 1, does it matter if they are out of
sequence, yet the numbers are sequential?

David