Sequential Number that matters

[Guest]

I've read through many posts on the subject of not using autonumber when you
need a sequential number that has meaning other than providing a unique id.

I have a table whose purpose is to generate a unique identifier of the form:
TM000000. That is, a sequential number between TM000000 and TM999999.
I was using an autonumber [ID] to help. I let it generate, and then I
UPDATE MyTable SET MyField = "TM"&Format([ID],"000000").

The inevitable happened, and my autonumber sequence got out of sequence. I
fixed it ala Allen Browne http://allenbrowne.com/ser-26.html, so I'm ok for
now.

The question is this:
If not Autonumber, what should I use to generate a sequential number without
skipping (much) or otherwise making a mess (I don't know: maybe someone might
somehow add a record with [ID]=99500, leaving me only 500 numbers left).

The question asked another way:
Is there a way to build into the table a generator of the next sequential
number without using Autonumber?

NOTE: Currently I'm using the table in datasheet view to add records; i.e.,
I do not have a form.

 

[Guest]

Use a query --
NextNumber:Max (Val(Right([MyField],6)))+1
This is assuming that you are using six digits.

It would have been better to add the "TM" prefix to the number in the report.

 

[Guest]

Unless there is a way to supply a value in the table itself, the way
Autonumber does, I will have to write a screen; when I do, I may use your
suggestion; however, I had planned to use DMax(["ID","MyTable") + 1 or
something. (I guess that's practically the same thing, eh?).

I cannot add the "TM" in the report, because there are many places to use
the TM-number, and they all requre the TM and zero fill.

Thanks anyway for your time and expertise.

Use a query --
NextNumber:Max (Val(Right([MyField],6)))+1
This is assuming that you are using six digits.

It would have been better to add the "TM" prefix to the number in the report.

I've read through many posts on the subject of not using autonumber when you
need a sequential number that has meaning other than providing a unique id.

I have a table whose purpose is to generate a unique identifier of the form:
TM000000. That is, a sequential number between TM000000 and TM999999.
I was using an autonumber [ID] to help. I let it generate, and then I
UPDATE MyTable SET MyField = "TM"&Format([ID],"000000").

The inevitable happened, and my autonumber sequence got out of sequence. I
fixed it ala Allen Browne http://allenbrowne.com/ser-26.html, so I'm ok for
now.

The question is this:
If not Autonumber, what should I use to generate a sequential number without
skipping (much) or otherwise making a mess (I don't know: maybe someone might
somehow add a record with [ID]=99500, leaving me only 500 numbers left).

The question asked another way:
Is there a way to build into the table a generator of the next sequential
number without using Autonumber?

NOTE: Currently I'm using the table in datasheet view to add records; i.e.,
I do not have a form.