Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2004
    Posts
    64

    Question Unanswered: start an AutoNumber at 1000

    Hi all,
    Just a question about access 2000 AutoNumber. Is there a way of starting an OutoNumber field at 1000 instead of 1?
    The properties I will use are:
    Field Size - Long Integer
    New Values - Increment
    Indexed - yes, no duplicates

    Any Ideas.
    Thanks

  2. #2
    Join Date
    Mar 2005
    Posts
    261
    Quite a few ways of doing it, one way is like this:

    Set AutoNumbers to start from ...

    (article by Allen Browne MVP)
    Last edited by waynephillips; 10-11-05 at 08:32.

  3. #3
    Join Date
    Nov 2004
    Posts
    64
    Nice reply but i dont understand the code or hw / where i would use it?
    Have you any more easier ideas on how I can start an autonumber from a specified value?

  4. #4
    Join Date
    Mar 2005
    Posts
    261
    Quote Originally Posted by Franki
    Nice reply but i dont understand the code or hw / where i would use it?
    Have you any more easier ideas on how I can start an autonumber from a specified value?
    The code is VBA - just open a new module and copy & paste the code. Then put 'Call SetAutoNumber("tblClient", 7500)' (as in the article) in the immediate window to run the code.

    Alternatively, create a new query, change it to an Append-Query, add the auto-number field, then put in 999 as the value to insert. Then run the query. Now open your table and you will have a new record with 999 - delete it. Now the seed value is 1000 for the next item.

    If you are familiar with SQL:

    INSERT INTO TableName ( FieldNameAutoNumber )
    VALUES (999);

    This assumes that the remaining fields are allowed to be null (i.e. not supplied) otherwise you need add the extra required fields to the append query too.
    Last edited by waynephillips; 10-11-05 at 10:26.

  5. #5
    Join Date
    May 2004
    Posts
    34
    The way that I have done this is to create your first record layout in Excel and start your number @ whatever. Then import the Excel file into your table. Be sure to turn your field that starts @ 1000 is autonumber. Next entry in the table will be 1001 and so on. This is the easiest way, especially if you are starting of your table empty.

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The 'easiest' way I know of is to add a blank to any field to create the first record. Then highlight the whole record, copy and paste it. Then highlight the two records, copy and paste. Highlight the four records, copy and paste. Etc. until you have 999 records. Then run a delete query to delet all the records. It may not be the most efficient, but I think it is the easiest to understand.

    Note: If you mess up, delete all the records from the table and compact and repair the database. This will reset the counter to 1.

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Ya want easy? Create a new table (NewTable) with a single field; call it Num. Make Num = 999. Now create an append query as follows:
    Code:
    INSERT INTO OldTable(YourVariableName) SELECT NewTable.num FROM NewTable;
    Works like a charm.

    Sam

  8. #8
    Join Date
    Mar 2005
    Posts
    261
    Quote Originally Posted by Sam Landy
    Ya want easy? Create a new table (NewTable) with a single field; call it Num. Make Num = 999. Now create an append query as follows:
    Code:
    INSERT INTO OldTable(YourVariableName) SELECT NewTable.num FROM NewTable;
    Works like a charm.

    Sam
    I believe I already posted a v. similar method (but even easier )

    INSERT INTO TableName ( FieldNameAutoNumber )
    VALUES (999);

  9. #9
    Join Date
    Oct 2005
    Posts
    2

    Smile

    Please do'nt think I am being sarcastic but when I needed to do this the office assistant had the answer which was not that complicated. In the search parameter enter "autonumber greater than 1" one of the selections should read "change the starting value on an incrementing AutoNumber field"
    Click this option and you should be on your way.

  10. #10
    Join Date
    Nov 2004
    Posts
    64
    Thanks all, finally chose the append query first submitted by WaynePhilips and then again an easier to understand append query.
    Kind regards to everyone.
    Franki

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •