Auto number

[petemul]

Hi hope someone can help with this; Access 2003

I am starting a new database and I am try to create a unique part number for items what i want to do is firstly enter a 8 digit code (alpha numerical) and then have a final auto generated 3 digit number but i would like it to start from 001 and countup in 1 for each combination of the 8 digit code.

to re cap the 8 digit code may be duplicated but not with the same 3 digit; example:

ABCD123A001
ABCD123A002
BBCD123A001

I'm not sure if i should have this as one number or two numbers and join them together in a different box.

what ever i try the auto number just counts up - the final digits are more like a record count.

thanks

[jimatqsi]

Pete,
Autonumber won't help you here, you'll have to do it the old fashioned way. In add mode, after the 8 character field is entered you'll need to put some code in the AfterUpdate event. Just loop 1 through 999, concatenating the loop counter to the end of the 8 character code and see if that key is already in your table or not. Stop at the first one that is not in the table and that is your new key.

Jim