Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2012
    Posts
    4

    Unhappy Unanswered: Create Unique Number in Access 2007

    Hi I am new to Access 2007. I need to generate a unique number in Job # field so that it will increase by 1 starting with 0001. I want to be able to assign this number after I hit save. There will be other users so I want to be able to prohibit deletions and prohibit cancelling the form so that the numbers don't repeat themselves. I don't want duplicate numbers. Do I have to create a new table or form from which to pull these number from? Please respond in layman's terms. I have tried various other sites that provided code but I didn't know where to enter this code and if I had to create other tables or forms? Anyone with any help would be very much appreciated. Thank you!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by mefrogs8 View Post
    I want to be able to assign this number after I hit save.
    Why don't you simply add an Autonumber column to the table where the data are stored?
    Have a nice day!

  3. #3
    Join Date
    Apr 2012
    Posts
    4
    I would use autonumber but there will be multiple users and I don't want duplicate numbers if by chance 2 people are creating a quote at the same time.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the system handles autonumber quite happily by itself.

    however if you need some form of external rules on your autonumbering (such as number must be contiguous there must be no gaps then an autonumber won't work. autonumbers should only be used when there is no external meaning to the number. an autonumber does nothing more than guarantee that no two rows will have the same value, ie guarantees that there is at least on piece of information that you can rely on to be unique.

    sometimes you use autonumbers becuase there is no preexisting column(s) that make a row unique.. .your quote number could do that
    or there are performance or storage reasons reasons (eg rather than use say 10 columns as your composite PK you could instead replace it with a single value.

    one of the problems of the autonumber in the Access / JET world is that it gets created/allocated when you open a form for addition, but will dissappear if the addition is cancelled leaving a gap in the sequence.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2012
    Posts
    4
    That is my problem. We can't have a gap in sequence, have any deletions, or cancellations because there would be a gap in sequence. I thought if there was a way that the number could be generated upon save and close and then the new number would pop up, but to actually get that to work I am not so sure of. I have the idea of what I want I just don't know how to execute it. Someone had given me a code I could use but they have me creating a new table and then gave me the code but I don't know how to link it to my form or where the code goes? It is using a Public Function fGetNewNum() As Long and a whole bunch of other code that goes with it but I have no idea where to put all of this?????

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by mefrogs8 View Post
    That is my problem. We can't have a gap in sequence, have any deletions, or cancellations because there would be a gap in sequence.
    This is precisely a situation where an Autonumber column ought to be used. It's the only solution that can guarantee that there will never be 2 identical numbers generated for the same table (see: Fields that generate numbers automatically in Access - Access - Office.com and also Identity column - Wikipedia, the free encyclopedia).

    Moreover, let's imagine the following scenario:

    1. Row for the Job #00001 is created.
    2. Row for the Job #00002 is created.
    3. Row for the Job ...
    ...
    8. Row for the Job #00008 is created.
    9. Row for the Job #00003 is deleted.
    10. What will be the next Job #? Will you have to renumber all jobs from 4 to 9 to avoid a gap in the numbers?
    Have a nice day!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    this is precisely the occasion when you don't use an autonumber, unless you have an autonumber column AND the sequence number, which is redundant. the only time in this model you might do this is if the say the sequence number is not known at the time of the row creations.

    you need to roll your own function that finds the current maximum number and locks the table, writes a row, then releases the lock
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Apr 2012
    Posts
    4
    Quote Originally Posted by healdem View Post
    this is precisely the occasion when you don't use an autonumber, unless you have an autonumber column AND the sequence number, which is redundant. the only time in this model you might do this is if the say the sequence number is not known at the time of the row creations.

    you need to roll your own function that finds the current maximum number and locks the table, writes a row, then releases the lock
    How would I be able to roll my own function that finds the current maximum number and locks the table, writes a row, then releases the lock? And could there be a message that pops up to a user that someone else is in that form and they have to wait until the other person finishes before they create their new record so that there is not duplicates? Thanks!

Posting Permissions

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