[AccessD] Custom autonumbering

As I pointed out, let JET do the work.  As long as you want sequential,
and you insert a new record (with the actual primary key) when your code
below would normally run, you have a sequential number, matched with no
worry about duplication, your actual key will be matched with a
sequential number (doesn't matter, in a multiuser environment, the
Autonumber would just be created and sequenced, automatically linked to
the primary key).  And you can set any starting (or new starting) point,
with a simple Append query.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Wednesday, July 01, 2009 7:13 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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

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.