MS Access auto numbering trying to start with different #?

Posted on 2009-02-13
Last Modified: 2012-05-06
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).
Question by:servent1
    LVL 119

    Accepted Solution


    Author Comment

    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.  

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now