Generate auto number like 100011,100012...

[lizzywu]

Hi there,

I’ve got a table in which subjectid, as the primary key, is an autonumber starting with 10001 (ending with 10100). I want to create another table with at least three different addresses for each subject. Is it possible to generate autonumber based on the way of setting up subjectid? Say, I’d like to have addressid to be
100011
100012
100013
100021
100022
100023
...
101001
101002
101003
Thank you very much!

[orange]

Autonumbers are NOT NECESSARILY incremental/in sequence. Autonumbers are intended for use by Access and are considered to be UNIQUE. If you need sequential numbers, and contiguous numbers, then autonumber is NOT the choice.

Autonumbers can be set up to start at a certain value, but they are NOT sequential necessarily.

[lizzywu]

You're right, Orange.
Do you know how to generate such sequential numbers? Thank you.

[orange]

Here's a method using DMax +1

http://www.599cd.com/tips/access/inc...r-own-counter/

[lizzywu]

Is it possible to do that without using a form?

[orange]

Yes, you can do it with vba.
NextSubjectId = DMax("SubjectID","YourTableName") + 1

Part of this method is to store the new Value in the table, then the next time you need a to increment SubjectId, you use the DMax("SubjectID","YourTableName") + 1

The way this works you assign the first SubjectId to a record in your Table.
Then Dmax says get me the Max value of SubjectId in "YourTablename" and add 1 to it. That's the number to store with the next record. Once it is stored, and you want to add a new record, you use the DMax("SubjectID","YourTableName") + 1 to get the next number and so on and so on.

[Access_Blaster]

What happens if the database is split, and two or three users are needing sequential numbers on unsaved records?