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.
Thanks for your response though, that'll probably come in handly later down the road.
