BaldyWeb
Custom Autonumbers/sequential
numbers
How can I generate a customized AutoNumber?
There a couple of answers to this common question:
The quick and dirty answer
Append something to a regular AutoNumber field.
Example: Let's say you want an AutoNumber like:
2003-0001
2003-0002
2003-0003
...etc...
You can't do this at the table level, as you can't make one field refer to
another. However, you could create a field (AutoDateField) with a default
value of Year(Date()) and always display the fields together:
AutoDateField & "-" & AutoNumberField
You can extend this concept of appending something to an AutoNumber field as
appropriate for your needs.
That being said, let's move on to...
The proper answer
You don't want an AutoNumber. The main problems with using an AutoNumber field
as a user-visible field:
- You are not going to be able to guarantee the number
remains sequential.
- You can not change the number.
- You can not "re-start" or skip numbers (in the above
example, you're going to have problem come 2004. Think about it.)
The point is an AutoNumber is intended to be an AutoNumber, not a
SequentialNumber. It's intended to provide a system-visible unique record
identifier, not a user-visible one. It's so close it really makes you want to
use an AutoNumber, but it's not close enough.
The proper way, then, is to create your own function that creates the number
sequence you need.
Have the last1 number stored in a table. It would probably be a good
idea to make this a one record table, with this value the only value in the
record. A one-value table.
When a new sequence number is needed, retrieve the number. Lock the table so no
other user can pull the same number until the current number is used. This will
prevent duplicate numbers and breaking the sequence.
After the record is committed, you can store the new number and unlock the
table.
There are some possible variations on this scheme. For example, you don't
absolutely have to store the number in a table. You could search the
target table for the most recent number and work off that. You do run into the
possible problem of two users trying to use the same number at the same time.
The method commonly used for this variation is the DMax() function. Given
our earlier example of a sequential number by year, that DMax() might look like:
Nz(DMax("NumberField", "TableName", "DateField = " &
Year(Date())), 0) + 1
The DMax() looks in the table for the largest value in the
field named "NumberField" where the year in "DateField" is equal to the current
year, and adds one to it. The Nz() function ensures that the first record
of the new year works correctly. You can use other fields instead of year,
to meet your needs. For example, some need numbers by department or
company.
Either approach has the potential to deny a user a number, though. It's a matter
of where you feel it is better to deny the number, before the record editing
begins, or during record editing.
1Or the current number - you can work it either way.
JasonM
Home