Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2010
    Posts
    6

    Unanswered: Autonumber...please help

    I create autonumber as primary key in a table, then i tried to enter a record in that table. Then i deleted it, but when i enter a new record, auto number started with the next number. Ex: i enter a record and autonumber for it is 1. I deleted this record and entered a new record, but autonumber is 2. I want the number to be 1

    I copied that from a page i found with google but i couldnt view the replies

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    the autonumber is operating correctly. its purpose is to generate a unique value that never repeats.

    for the reason you have just described - it is not suitable as a visible 'numbering' method - only as a key field in the background.

    your confusion is not unusual among new users. There is not in fact any simple embedded method to have a line numbering system - part of the reason is that users might sort a column/field alphabetically (that's an embedded feature of any db) and so the numbering would no longer be in order. Plus of course as you see if any record is deleted - are you going to trigger a query that renumbers every line??

    The simplist thing is to add a number field and let the human enter the value. But with a little vba you can do a Max+1 technique so that the db auto enters the value - - which is suitable for the moment of data entry - - but still doesn't deal with the issue of what happens should a record be deleted and how to renumber everything....

    note however over on the Report side - there is a line numbering technique that works when the report is generated....

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    If you want the AutoNumber field to begin again at 1 (and the table has no data in it) then you can compact the database and that will 'restart' the AutoNumber field.

  4. #4
    Join Date
    Jul 2010
    Posts
    6
    Thanks for the help guys. I will try using the max+1 technique

Posting Permissions

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