Results 1 to 6 of 6
  1. #1
    ceatana is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Posts
    22

    Assigning unique id numbers


    I have a database of members and I would like to assign a unique, incremental, membership ID to each person as they are added to the database. Everything I've read says do not use autonumber to do this. Is there an easy way to have the ID number auto-populate?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    easy yes, use autonumber.

    if you do not use autonum, not so easy.
    you must get the last # of the last customer,
    increment 1
    assign it.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,821
    I have read that autonumber can generate negative or non-increasing values, but I have never seen that.

    Autonumber PK not supposed to have meaning to users. It is just a way to create a unique identifier and link related records.

    If you must account for every number, in other words, cannot have gaps in sequence, autonumber may not be best. Because when a record entry is aborted, the generated autonumber is 'lost' resulting in sequence gaps. There are ways to deal with this but is not simple.

    Generating custom unique identifier is a common topic. Here is one https://www.accessforums.net/showthread.php?t=23329
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I've never understood the reluctance to use an autonumber as a unique "Account_no" or "User_ID" , it's just a meaningless identifier.
    Like a social security number or bank account number. ( I know that's not a great comparison but you get my drift)

    We use them as Account numbers, Job numbers, return references, all sort of things, and the fact you might miss some in a sequence or they have jumped a few thousand (very rare) has had no impact on their use whatsoever.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Only cases when you certainly must not use autonumbers are:
    1. The number of records may grow bigger than max long integer (-2,147,483,648 to +2,147,483,647, take into account gaps from deleted records etc. and let it be 1,500,000,000);
    2. The table is truncated and rewritten continuously. Usually this is used when using some automation to read data from 3rd-party databases - it is usually faster to clear old data and insert new, instead to run 3 queries to delete, add and update records.

  6. #6
    ceatana is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Posts
    22
    Thank you all for your help. It looks like I should be able to use Autonumber for what I want.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dmax Help with unique numbers
    By baronqueefington in forum Access
    Replies: 5
    Last Post: 12-30-2014, 12:50 PM
  2. Replies: 5
    Last Post: 09-14-2014, 11:29 AM
  3. Assigning multiple sequential numbers to orders
    By jree3000 in forum Programming
    Replies: 14
    Last Post: 09-09-2014, 04:35 PM
  4. Replies: 3
    Last Post: 01-28-2014, 02:33 PM
  5. Assigning numbers to certain fields
    By smartflashes in forum Programming
    Replies: 6
    Last Post: 01-19-2012, 05:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums