asked on

MS Access auto numbering trying to start with different #?

Need to have auto numbering start with say 1000 instead of just 1. Now
I pulled a script off the web to start it at any number but it still goes
back to 1, ie, I put 999 in the first record but then the next record is
a 1.

How to get it to go off the last number, ie, 1000, 1001 ...1002 etc?

Note, I'm also appending a table to a table (the first table I'm appending has no
auto number field so that won't effect the auto number table).

And the auto number table has NO primary key (for now).

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

servent1

ASKER

Capricorn 1,

Thanks Mate! Was a little tricky when I appended my original table to the temp
table with the new start value. I had to delete the auto start field in the original
table otherwise it would append the old values.

I don't know why MS doesn't have a field call 'start value' in the auto number field. How
fricking siimple that would be!

Here is another way to create the start value in script I found on the web:

Create a new query (don't base it on any table or query) and switch to SQL view and paste the following code into the SQL window:

PARAMETERS [Number] Long;
INSERT INTO NameOfTable (NameOfAutoIDField)
SELECT [Number] AS Expr1;

Type the exact name of the table that you want to change the autonumber where I have NameOfTable. Type the exact name of the field that is your autonumber field where I have typed NameOfAutoIDField.

Press the Red Exclaimation icon on the tool bar to run the query. A parameter form will open that says number. Type one number less than you want to start at. For example if you want the number to start at 1000, then type in 999. Then the next new record will now be at 1000.