Autonumber question

  • Thread starter Thread starter Joe Williams
  • Start date Start date
J

Joe Williams

ok, I have read a lot of thread about not using the autonumber field as a
sequential identifier for a record, so here is my question:

What is the general process that will read the previous record, increment it
accordingly, and then save that record so that I can have a sequentially
numbered field? How does this work in a nulti-user environment? Can I then
use this field as the primary key? What then is the purpose of the
autonumber?

Thanks

-joe
 
An autonumber field is fine as the primary key field of a table - it is
merely a unique number that can serve as a single field primary key. As long
as you and your users attach no other importance to it, it is fine.
Unfortunately, many times we need a numeric field like an autonumber which
needs to be a bit more controlled (checknumbers, invoice numbers etc.) -
this is when we recommend creating your own counter field. Often, there is
no real business need for controlling the number but the users get so
attached to the concept of sequenced identifiers that they insist on having
"meaningful" numbers.

The easiest way to create a custom counter is using the Dmax function where
you grab the highest existing value from the table and then increment it.
This method is fine in some environments but not in a multiuser situation
where 2 or more people are simultaneously entering records into the same
table.

For example: You have a table tblCustomer and you want to increment Custid
without using an Autonum field. The following will get the current max value
of Custid, increment it by 1 and then put the new value into the control
me.custid:

me.custid=nz(dmax("Custid","tblCustomer"),0)+1

Another method that works well for the multiuser situation is create a table
which has only one row. This table holds the next available number for the
counter. Build a function, GetInvoiceNum (or whatever you want to call it).
This function will open the table with the dbDenyRead option, which prevents
other users from opening the table until this instance of the function
closes it. The function gets the next value (the return value of the
function) and the increments it and updates the table. In the error handling
of the function, if the table can't be opened because it is locked, your
function should wait and then try again.

For more info on one method for this see:

ACC2000: How to Create a Multiuser Custom Counter ID: Q210194
http://support.microsoft.com/default.aspx?scid=kb;[LN];210194

Another resource is the Access 20XX Developers Handbook (Volume 2), Litwin,
Getz and Gilbert which has some code for this which you can probably use as
is in your project.
 
Joe Williams said:
ok, I have read a lot of thread about not using the autonumber field as a
sequential identifier for a record, so here is my question:

What is the general process that will read the previous record, increment it
accordingly, and then save that record so that I can have a sequentially
numbered field? How does this work in a nulti-user environment? Can I then
use this field as the primary key? What then is the purpose of the
autonumber?
Click to expand...

Last question first. AutoNumber's purpose is to provide an automatic value
that is "almost guaranteed" to be unique. Any attribute beyond uniqueness
cannot be achieved using an AutoNumber. They work particularly well as the
field to be used when creating relationships between tables. Where the
natural Primary Key candidate might consist of numerous columns using an
AutoNumber means that you can use a single field in the relationship
between the tables instead of having to add multiple foreign key fields.

There are variations on two general themes when "rolling your own"
auto-sequencing number. One is to simply query the data table for the
highest existing value, add one to it, and then assign the result to the
new record. Where you do this has a great deal to do with whether you have
a single-user app or will have to deal with concurrency.

In a single user app you could simply use the DefaultValue property to
assign the value providing you are not using a continuous or Datasheet
form. In a Multi-User app or continuous form you would use one of the form
events to do the assignment as the DefaultValue method will not work. I
use the BeforeUpdate event as it occurs just an instant before the record
is committed to the table so it provides the smallest window of time where
two people could come up with the same value. Since BeforeUpdate fires
more than once in the lifetime of a record, you need to add a If-Then block
so that you only assign numbers to records that do not already have one.

The second popular method is to have an additional table with a single row
that contains the Last-Used numeric value. In the same form event as
above, you would retrieve this value while simultaneously placing a lock on
the table. You add one to the value retrieved, use the result in your new
record, and also write this new number back to the source table after which
you release the lock. This method has the smallest chance for two users to
grab the same value (should be impossible due to the lock you place on the
table), but is a bit more work to set up.
 
Back
Top