[AccessD] Custom autonumbering

Hi Asger, what's the reasoning for just not using an Autonumber field?
It looks like you do want it sequential.  You can start it at any number
you want, by appending the number (-1) into the table, then compacting
the database, and deleting that appended record.

Now, if it must be sequential, and you need to provide the ability to
allow for slips, which would negate a normal Autonumber field, what I
would recommend is to use two autonumber fields.  One in the primary
table, with the actual data.  Make this the actual key too.  Then, when
an 'order number' is really ready to be created (so when you would run
your normal code), have a second table with another Autonumber (which
would be the order number), and a plain long integer as the foreign key
from the data table.

That will let you keep the second table sequential, while not having to
worry about duplicates.....

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Thursday, June 25, 2009 7:31 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Custom autonumbering

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?

Asger


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the person or entity 
to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
Sensitive material. If you are not the intended recipient, please contact the sender 
immediately and destroy the material in its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure, dissemination, 
or other use of, or taking of any action in reliance upon this information by persons 
or entities other than the intended recipient is prohibited.