Jumping AutoNumbers RRS feed

  • Question

  • I have 3 interlocking companies using different front ends all hooked to the same backend. One of the things that this allows us to do is to have 2 of the companies "accept jobs" from the borker and append the info so they don't need to recreate the jobs. They also create jobs from other sources where they need to create the estimate and the job. All 3 companies use autonumbers for their jobs. In 7 years, I have had a small handfull of skipped numbers from the broker. I n the 2 years that they printing subsidiary has been on board, I have had 2 or 3 numbers skipped. The finishing company has been on board for 3 months. they use the exact same procedures as the printer to accept jobs and all 3 companies use the same procudures to go from estimate to job, yet the finishing company is constantly (3 to 10 a week) skipping auto numbers. They are skipped both when accepting jobs and on estimate to job although the later is very infrequent because it is only about 5% of the business.

    Does anyone have any ideas? I have recreated the Jobs table from scratch twice and it still is happpening.



    Tuesday, May 8, 2012 2:15 PM


All replies

  • Perhaps the employees of the printer company cancel new records more often? When a user starts creating a record, a new AutoNumber value is assigned. If the new record is cancelled, that AutoNumber value vanishes into thin air, it will not be reused.

    Is it really a problem if there are gaps in the sequence? An AutoNumber field is primarily intended as a meaningless unique record identifier.

    If the gaps are not acceptable, you should not use an AutoNumber field, but use code to assign the next available sequence number.

    Regards, Hans Vogelaar

    • Proposed as answer by Vanderghast Wednesday, May 9, 2012 6:20 PM
    Tuesday, May 8, 2012 2:55 PM
  • Hans, thanks for your input.

    All the jobs are being created through append queries in the case of all 3 companies and both when "accepting jobs" and when they create a job from an estimate. An occasional skipped number hasn't been a problem but this is happening so often that it would be leaving constant gaps. And as I said, all the others have been working for years without a problem.



    Tuesday, May 8, 2012 3:10 PM
  • I can't explain why that happens, but again - gaps shouldn't be a problem, but if they are, you should assign the numbers yourself instead of relying on AutoNumber. See for example Scott Diamond's http://scottgem.wordpress.com/2009/11/25/sequential-numbering/

    Regards, Hans Vogelaar

    • Marked as answer by tgavin Thursday, May 10, 2012 10:08 AM
    Tuesday, May 8, 2012 3:29 PM
  • Hi Terri.  Not sure I understood correctly, but:

    An autonumber may be skipped when you enter a new job, decide not to save it (escape twice) and reenter another job and save. If you undo several times before you save, autonumber will advance for each undone job. I don't know how to change this behavior, but have been told that using autonumbers as identification is not a good choice. In the old days of database replication this would have been impossible to do, since a replicated database used a random number (and not a sequence nbr) as autonumber.

    Hope this explains?


    Tuesday, May 8, 2012 3:37 PM
  • (...) have been told that using autonumbers as identification is not a good choice.

    Huh? AutoNumbers are an excellent choice if you want an easy-to-use unique identifier for a record. But they are not suitable if you need "meaningful" sequential numbering.

    Regards, Hans Vogelaar

    Tuesday, May 8, 2012 3:43 PM
  • If someone goes to create a record it assigns the next auto number temporarily and locks the record so it cant be changed by another user until the original user is out of the record.   Then while that number lets say 5 is set aside for that particular record, someone at another office creates a new record and the number assigned is 6, then the first user cancels his record from being created and the number 5 is returned to the list as never been created.  Had someone else not been in the database creating another record the record 5 would be the next auto number, however since someone has already made 6, auto number continues its next number from the 6 and there is no 5.  

    Sorry if that is a little confusing but thats whats going on.

    Tuesday, May 8, 2012 4:49 PM
  • Thank you all for replying. I guess I have a fluke with this one that I will use Han's suggestion and the info from the link to fix. There is only 1 person using this part of the db, so it's not other users. I have used AutoNumber for my order, estimate, etc numbers but this one I will need to write the code for.




    Thursday, May 10, 2012 10:11 AM
  • Had someone else not been in the database creating another record the record 5 would be the next auto number

    That's not the case.  Once generated an autonumber value is not regenerated unless the database is compacted/repaired.  It does not require a row with a higher value in the autonumber column to have been inserted.  In the absence of a compact/repair of the database it would require an INSERT INTO statement to be executed which specifically assigns the value to the autonumber column to insert a row with a value in the column from a previously deleted or uncommitted row.

    Ken Sheridan, Stafford, England

    Thursday, May 10, 2012 6:17 PM