How do I reset autonumbering without affecting previous records?

[Guest]

Basically I need to calculate a unique number to store in a Microsoft
Accessdatabase which is auto generated when I enter a new record. The number
should be in this format xxxx-001 (the first part reflecting the current
year) and the second part being sequential (with 001 being the first record
of the year)

I thought it could be done by I creating a recordset that retrieves all the
records for the current year then counting to determine the next number which
is also another recordset. If the year is the same, then I'd count the
number of digits and append zeroes to make the value a certain length and
then add the year in. But, if the year is new and there are no records based
on the count, then I'd start the count at 001. PLUS - I'd like this number
to autogenerate when I save the new record - possible put it behind a button
object. But I haven't been able to get it to work.

 

[Ken Ismert]

Well, here are some thoughts:

* You can't use a built-in Autonumber field because you require a
specific format for your key. The intent of Autonumber is that it be
'meaningless', or just a unique ID. Your field has meaning (Year-Count)
and so can't be Autonumber.

* You should consider using an Autonumber field as your primary key,
and use your formatted field as the display, or business key, which
would also be uniquely indexed. Your foriegn tables would key to the
Autonumber field. The advantage here is, if you decide on a new display
number format (say, Year-Customer-Count), you can redefine it without
messing with the IDs stored in the foreign tables.

* To generate your formatted key, keep a separate table that holds the
last key in one row. When you add a new record, call a function that
reads the last key value, creates the next valid value, and updates the
row in the key table. The function then returns the new key for your
new record.

-Ken