Autonumbers

[Someone]

Hello

I need to give users a unique reference for every record they create.

I know that autonumbers shouldn't be used as meaningful information and I
also know that sequential autonumbers can be created using
DMax("ID","TableName)+1.

Is using the DMax option acceptable for representing a user-visible
reference field (as well as the primary key), or should one have a secondary
field specifically for this purpose?

I'd like to make sure I'm taking the best approach!

Many thanks
M

 

[Douglas J Steele]

The DMax approach can run into problems if you're in a multi-user
environment. It's possible for two (or more) users to arrive at the same
number. Other than that, there's nothing wrong with the approach.

If you are in a multi-user environment, either ensure that you can trap and
handle duplicate PK errors, or look at using an approach that uses a second
table that holds the next number to use, and use transactions to ensure that
the reading of the next number to use and incrementing of that number for
the next time is done as one operation.

 

[Sandra Daigle]

It all depends on the number of users adding records and frequency of
additions to the table since the Dmax method doesn't gaurantee that users
will get a unique index. There are other more reliable ways to ensure that a
user gets a unique index. One 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.

 

[BruceM]

For another approach, see Roger Carlson's example:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

 

[Tony Toews]

You can use one field. In fact you can use Autonumber IF the users are
not going to expect consecutive numbers and will not be confused. There are
some situations where that would be acceptable.
Agreed.

Or you might use random
autonumbers so they would never see a consecutive numbers and would be less
likely to become confused.

I dunno about that. Those random numbers be definition will generally
have a lot of digits in them. Usually about eight digits. I suspect
that won't be all that practical.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Someone]

Hello all

With many thanks to you all for your input. I don't know what I'd do
without you!

Thanks
M