Autonumber generator

[Tony Williams]

Could someone point in the right direction for help on creating an
autonumber generator? I've searched this news group but cant' find anything
as simple as what I need. I don't want to use the built in Autonumber
because I understand there is no control over the creation of the numbers,
they aren't sequential if records are deleted. I want a simple number
created starting at the last highest number already in the control and it is
a 3 digit number, nothing fancy just 3 numbers. My records will start at 001
and at the moment there are 51 records so I want the next number to
generated as 052 and so on. This number will only be generated for new
records.
TIA
Tony

 

[JohnFol]

But what happens if say, the record whose number is 021 gets deleted?

 

[Tony Williams]

Hi John, That's OK not a problem, as long as the user is in control of the
numbers and not Access
Tony

 

[JohnFol]

You could do an OnCurrent to set the value when the user first gets to the
record

something like

If isnull(MyIDField ) then

MyIDField = format $(DMax("MyIDField","MyTable) + 1, "000")

End IF

(Not tested but you get the gist)

 

[Ed Robichaud]

If you search this group on "autonumber" you'll find a lot of advice.

Autonumber is a field type that is really a long integer, with some hidden
code
to increment the value for every added record. I do NOT recommend
any of these pseudoID forms that use combinations of initials/date/other,
as they ALL will eventually fail and most are vulnerable to user input
mistakes.
Trying to "imbed" data within an ID is almost always a bad idea.
Most good data designs have a primary key for each table,
generally an autonumber field. Most applications do not display that ID, but
use it internally for relational integrity.

In short, the value of the autonumber field is irrelevant; it's real purpose
is to generate a unique value. Most (if not all) the home-made systems will
not reliably do that.

-Ed