[AccessD] Custom autonumbering

Asger, there is only one real issue with your solution, and that is the off chance of a clash with multiple users (since your code is looking for the max value and adding 1, that is slower then Jet handling an autonumber automatically).  You solution is simpler to implement on an existing system.  On a system being built from the ground up, both would be about the same to implement.

Drew

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

Max, I have always had the same opinion about AutoNumber as you (only to use
it for surrogate keys). Drew has pointed out a solution though using two
tables which make an AutoNumber applicable for alternate ("natural" or
"meaningful") keys.
One thing I don't understand is why you too use two tables since your
solution is applying the DMAX function. Why not just use a plain Long
Integer and then on the form set DefaultValue to Dmax + 1, and supply an
event procedure to manage potential conflicts in a multiuser environment?
This was the approach I was suggesting in my first posting, and it seems a
lot simpler to me. Do you see any pitfalls in this way of doing it?

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Max Wanadoo
Sendt: 2. juli 2009 17:16
Til: 'Access Developers discussion and problem solving'
Emne: Re: [AccessD] Custom autonumbering

Asger, I have just read all your postings in this regard and it does appear
that you want your autonumber to be "meaningful".  If that is the case,
don't use autonumber.  As many previous postings here will point out, the
whole of idea of an autonumber is that is has no meaning other than it is
unique within the database and can act as a pointer to the record.

IF you want a meaningful sequence in the way you describe with differing
start points, one way would be to have a table holding a field called the
"Start Sequence" as you mentioned before and then using Dmax to get the next
number.  When a new sequence is started, add it to the table.  If it is
already there, just increment it.  Dont allow the same Start Sequnce to be
duplicated.  If you want to ensure that no two people can get the same
number at the same time, have a lock-table which holds the tablename and
fieldname being accessed and don't allow others to use it until it is freed
again.  You can put a TIMER on when it locked and if not unlocked in x
seconds, then unlock it and release the TIMER.

Max


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.