Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2009
    Posts
    39

    Unanswered: Need code to increment an ID field for new records

    I'm currently building a form that has a 'Log#' field that gives an Id to each record entered in by a user. Since autonumber shouldn't be used for this I want to do it with code that populates the Log# field in the form and increments it by 1 for each new record that is added to the Conference Record 2011 table.

    Thanks.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why won';t an autonumber cut it?
    the only reason I can think of is if you have people insisting on a contiguous number set. if they insist, ask 'em to justify such a user requirement.

    Im also suspicious of tables which have a year in them, unless you have a good design reason to split data into arbitary groups (such as 2011, mayinvoices and so on
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2009
    Posts
    39
    We'll I've read many threads about this topic and people usually say that autonumber shouldn't be used for putting an ID on records and that a human should fill out the field. I've used auto number for this in the past and for some reason it will start skipping numbers even though no one has deleted a record, I.E say one week records 301-400 are fine, then two weeks later I notice it goes from 301-349 then 351-400 even though no record has been deleted it skips one and causes the numbering to go out of order for the rest of the records which we dont want happening since we also print off hard copies of the form for each record, we dont want the printed form to say Mrs.jones ID is 250 but in our database have it says Mrs.jones ID is 251, its not professional to our customers.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    If the id is in the database as 251, why would the form print it as 250? Also, the reason you probably end up with gaps without a record having been deleted are due to either the CACHE parameter was used, to cache a certain number of ids at a time and then the database was restarted, which would cause you to pick up the next set of n records. The other possible reason is a number was assigned in one transaction, but not committed and that transaction ended up being rolled back.
    Also, who cares if there are gaps???
    Dave

  5. #5
    Join Date
    Jul 2009
    Posts
    39
    Because the record changed in the database from 250 to 251 after we already printed the record out when the record was at 250, we care if there are gaps for just that reason, then when a user tries to look up say record 300 and her hard paper copy says that record 300 is Mr.smiths info but in the database it says record 300 is Mr.bobs info because it changed on its own then the user freaks out and calls me. Do people not really care if thier ID numbers for thier records goes crazy? that one day this specific customer has a certain ID, then the next day the same customer has a totally diffrerent ID and that shouldnt bother anyone?

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you didn't mention that somehow your app/system is arbitraliy changing id numbers with the database. That would still have nothing at all to do with auto generated numbers, it would have to do with someone's code is doing something abherrent.
    Dave

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry
    you cannot change (modify) an autonumber column, at least not to my knowledge.. mebbe its a 'feature' of a specific more modern build of JET, but not in any version I've used. yes you can change the values of the other columns but you cannot change the autonumber column's value

    its sounds more likely that someone is editing records and denying it. in part thats why I'm a great believer in tracking changes with the userid, date & time and computer name of the person who made the changes. that way round if it comes to a blamestorming session I'm in the position of playing computer Cleudo (except I know who killed Dr Black, with what computer and when. theres no finger pointing at me for the computer doing silly things.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jul 2009
    Posts
    39
    ok thanks for the help guys, I will use autonumber for keeping track of records, I'll also look into tracking changes that a user makes that healdem mentioned, do you have a link to any tutorials pertaining to tracking changes/history in a database? If not I'm sure I can find something.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by healdem View Post
    sorry
    you cannot change (modify) an autonumber column, at least not to my knowledge..
    This. If you ever want to reseed an autonumber column to "fix" gaps, you have to physically create a NEW column and let it autonumber itself, then take care of whatever relational stuff you need to do before eventually deleting the old column.

    Autonumber will not allow a change to the id of an existing record. It won't let you do it, it won't do it on it's own. Ever. If a gap appears due to a record being deleted, it's there forever.

    If you need to maintain a sequential number for human consumption, it's best to manually do that independent of your primary keys.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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