Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50

    Angry Unanswered: Autonumber & Increment Number field

    Hey,

    I have an ID field in a table that was originally set as a number field. The table is now a data entry form with a sub form. I had code in the On Current event in the main form telling the ID field to increment 1 number when the a new record is added.

    Access to the subform is by a button. The code for this button is now in the On Current event of the main form and the incrementing code "has vanished". I tried putting it back, but I'm getting an error.

    I then tried to change the ID field to autonumber (which I should have done in the first place). I tried adding a new record and it was partially working. I deleted the record it was fine. But the problem is that when I try to add a another new record the ID field is incrementing to the other number in sequence after the previous number was deleted.

    add first new record - 9
    delete record - 9
    add second new record - 11
    delete record - 11
    add third new record - 13

    the add and delete buttons were made through the wizard. Any suggestions on why this is happening?

    BajanElf
    "The extreme always make an impression." - Jeff Hardy

  2. #2
    Join Date
    Feb 2004
    Posts
    142
    The auto increment will increment the number even on failed inserts. The wayit works is the number is incremented before an insert and does not go back to unused numbers. It is designed to give unique keys and not to get a complete serial number set.

    If you need a unique key then auto number will work. If you need to have a full number sequence then you will have to go to code to do it.
    KC

  3. #3
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50
    Thanks for the insight. If I do use the incrementing code I had before where would I put it? I had it in the On Current event of the main form, but the incrementing code is causing a problem.



    bajanElf
    "The extreme always make an impression." - Jeff Hardy

  4. #4
    Join Date
    Jan 2004
    Posts
    492
    My 2 cents would be to use the auto-number for generating unique PK's. It is not realistic in any system to have numbers in perfect sequence with no spaces in between. There will always be records with gaps in them - usually the id is not something the end user will see anyway. Its more something to keep the key unique and allow for normalization of tables.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by ss659
    My 2 cents would be to use the auto-number for generating unique PK's. It is not realistic in any system to have numbers in perfect sequence with no spaces in between. There will always be records with gaps in them - usually the id is not something the end user will see anyway. Its more something to keep the key unique and allow for normalization of tables.
    Well sure you can ... I do. I don't like autonumbers for the reason that there is the possibility of gaps ... I use the old tried and true method of having a table with "next number" for various IDs in it and run my own routine to retrieve that next ID ...

Posting Permissions

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