Results 1 to 8 of 8
  1. #1
    Falkirk is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    4

    Generate part number which consists of category and number

    I am creating a DB where all our purchased and manufactured parts are indexed in a "Parts" table with a Part Number field Name and given a part number in the format of ###-####. Obviously the part number will have to be unique for several reasons. I have a table containing the categories which are numbered from 101 to 999.


    My question is "which is the smartest way to populate the second half of the part number?"
    When entering a new number for a part the first section is determined by the category - selected by the user. The second part should be auto filled with the first available number in the range of 1001-9999 whilst creating a unique part number "###-####" The last four digits are not to be unique occurrence.

    Is there a built in way of doing such things, would some VBA be the way to go. I am also thinking in terms of speed when creating a new part.

    Any input would be welcomed.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Generating custom unique identifier is a common topic. Start with review of 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.

  3. #3
    Falkirk is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    4
    Thanks June. I did suspect it had to be done via VBA.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    why does it have to be for what you describe? You have categories, so ### is going to be driven by a form combo selection? Then this
    The second part should be auto filled with the first available number in the range of 1001-9999
    means you will need a table of values with a way of denoting which ones have been used already. A few ways to do that. Thus getting the next number can be as simple as a query or DLookup. The biggest hurdle in trying to make these sequential will be dealing with the potential to "grab" one of these numbers and not complete the record thereby losing it, or having 2 or more people doing the same process at the same time. That situation will exist regardless of what approach you take.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Falkirk is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    4
    Well the numbers already used will be entries in the Part table field Part Number. The last four in itself is not unique only the whole string of ###-####. My concern is only the parsing of used numbers in order to find the next available. I would like to avoid having 899 tables with each 8999 entries in ..

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I would like to avoid having 899 tables with each 8999 entries in
    Whaaa? Sorry, bu that makes no sense to me.
    You have a table for every category or something? For this portion you shouldn't need more than one Parts table, one Category table and one number seed table. You get the Min of number from tblNumbers where that number is greater than the Max of numbers for a category (actually the Min that is greater). So if the last part was ABC-1234, the min of numbers that is greater than the second part (1234) is 1235 so the new number is ABC-1235. No 'flag' needed to know which has been used or not. Regardless, tblParts should have PrimeNum and SecondaryNum fields, which taken together, have a unique index. Your form can concatenate them and insert the - character. Otherwise, to search on all ABC parts your parts table would need a field for the category (redundant even if it is only the PK value from Categories) or functions/operators such as Left or LIKE - not necessary.

    I'm not saying you should or should not use a code solution and I might opt for the coded approach myself. I just question the statement that it has to be code.
    Last edited by Micron; 07-01-2019 at 09:16 PM. Reason: clarification

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I do this without a 'seed' table. My process looks up the max SampleNum and increments and immediately commits record to table then opens form to that record for user to enter remainder of data. The ID is generated and record pre-committed because user has to write this ID on submittal paper and waiting till end of data entry increases risk of multiple users getting the same ID. Alternatively, ID could be generated at end of data entry just before committing to table and now I think I could have employed a MsgBox to display the ID. Either way, risk of multiple users getting same ID is greatly reduced though not impossible. However, in 10 years of use, never happened - code is fast enough.
    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.

  8. #8
    Falkirk is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    4
    Quote Originally Posted by Micron View Post
    Whaaa? Sorry, bu that makes no sense to me.
    You have a table for every category or something?
    No and that is exactly what I am not going to

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

Similar Threads

  1. Replies: 1
    Last Post: 10-07-2018, 10:21 PM
  2. Replies: 2
    Last Post: 08-04-2017, 12:37 PM
  3. The Category ID number System
    By Lou_Reed in forum Access
    Replies: 2
    Last Post: 10-05-2015, 12:18 PM
  4. Count number of defects per category
    By HeadGasket in forum Queries
    Replies: 5
    Last Post: 02-10-2012, 04:13 PM
  5. Replies: 2
    Last Post: 04-13-2011, 09:42 AM

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