Results 1 to 5 of 5
  1. #1
    DanT is offline Novice
    Windows 11 Office 365
    Join Date
    Sep 2022
    Posts
    23

    Auto Number for Product Code?

    Hi there,


    Is it a good practise to use auto-number function for generating product codes? If not, what's the best way to tackle it?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What will be the purpose of ProductCode in your proposed system?

    Here's an overview of autonumbers:

    UtterAccess -Autonumbers
    What they are NOT:
    1. Row (record) sequence numbers.
    2. An "order of entry into the table" number
    3. A "gapless" series of numbers.
    4. Editable numbers.
    5. A series of (necessarily) always increasing numbers.
    6. Intended to be viewed/used by end users of the application.
    7. Predictable (as to what the previous or next one in the table is/or will be).
    8. Reassigned, once deleted or discarded
    9. A predictor/indicator of the number of rows in a table.
    10. Intended to be used to "rank" or "sort" or "number" rows returned from the table.
    11. Necessarily used to determine the default order the rows may be returned from the table.
    12. Indicative of or related to any TimeStamp field that may also be in the table row.

    What they are:
    1. Unique numbers used to identify individual rows in a table.
    2. Automatically created by Access when a new row is "instanced" by Access.
    3. Great/Outstanding/Essential for use as the Primary Key of a table.
    4. Great/Outstanding/Essential for use as "link points" by Foreign Keys in other tables.
    5. Unchanging, once assigned to a particular table row

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    In ERP-systems usually products/articles/services etc. have autonumeric ID, and additional product/article/service code/number , with an unique index (not primary key, PK is ID!) based on this field. This code/number field may be structured, which allows to group different products, or such grouping is achieved using additional fields instead and product codes/numbers are used to keep historical codes/numbers in use.
    Last edited by ArviLaanemets; 09-11-2022 at 11:47 PM.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Using autonumber in an identifier that has meaning to users is possible and certainly been advised by many as alternative to the complications posed by programmatically generating a sequential/increasing custom unique identifier. Have to understand and accept/deal with autonumber features such as gaps in sequence due to aborted record creation or record deletion. Format the autonumber to display a consistent pattern. For instance Format(1,"0000") will result in a string value of "0001". Can concatenate static strings and/or other fields: Year([DateOrder]) & Format([ID], "-0000A") could return "2022-0001A". That value represents a sequence I must manage in a laboratory sample testing db. Unfortunately, I can't allow gaps in sequence and must restart at 1 each year so I programmatically generate the sequence.
    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.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Just one thing to add to the above answers.
    There are two types of autonumber - increment and random (which is much less commonly used)
    The above comments apply to both types except that random autonumbers do not increment and can be negative as well as positive.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 6
    Last Post: 01-26-2020, 04:15 PM
  2. Replies: 5
    Last Post: 10-02-2019, 01:35 PM
  3. Replies: 2
    Last Post: 11-26-2018, 02:16 AM
  4. Replies: 6
    Last Post: 02-28-2015, 11:57 AM
  5. Replies: 3
    Last Post: 01-18-2015, 06:05 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