[AccessD] Custom autonumbering

Asger Blond ab-mi at post3.tele.dk
Wed Jul 1 17:57:58 CDT 2009


Actually I don't want to mimic the AutoNumber field type. I want a custom
autonumber which is 1) reliable sequential, and 2) customable. Your
two-table solution provides the first requirement and I will consider it for
future use. But sometimes I need to supply an easy way of creating a new
offset for the autonumber: e.g. using the current year as a leading number
the user should have the ability to change the number let's say 20098001 to
20100001, making this a new starting point for the sequential numbering
(normally this custom autonumber field should be locked on the form and only
editable providing a password).
I still find the DMAX-solution easier to manage for the second requirement.
But as said I appreciate your proposal for a reliable sequential numbering.

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Drew Wutka
Sendt: 1. juli 2009 23:52
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Custom autonumbering

I've used Autonumbers as actual values (like Ticket numbers in a request
system) where the number doesn't have to truly be sequential.  But in
the case you are talking about, it seems kind of odd to create a routine
that mimics the AutoNumber field type, instead of just using that field
type (in a separate table to keep it sequential).  Hope it works out.

Drew

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

Thanks Drew - your explanation corresponds my reading your first
posting.
I always use AutoNumber exclusively for surrogate primary keys, never
for
alternate natural keys like OrderNumber.
In my opinion AutoNumber (and the equivalent in SQL Server: Identity) is
designed for surrogate keys, not for natural keys.
You could use AutoNumber for natural keys, but then you are inviting
troubles. In Access pressing Esc on entering a new record will increment
the
AutoNumber, leaving gaps in the numbering. And depending on which
version of
Access you use, a Compact Database will reset the autonumber to the next
value after your records INcluding deleted trailing records, or it will
reset to the next record after your records EXcluding deleted trailing
records. Bottom line: You simply can't predict the value of an
AutoNumber
field.
This is what makes AutoNumber an excellent candidate for surrogate
primary
keys because this kind of keys is supposed to be meaningless.
You can ignore the special behaviour of AutoNumber if you use it as a
surrogate key like OrderID, but you certainly can't ignore it if you
want a
natural key value like OrderNumber, which is supposed to be meaningful.

Asger





More information about the AccessD mailing list