Results 1 to 11 of 11
  1. #1
    JMac is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2012
    Posts
    21

    Generating Part Numbers From Field Entry

    Hi all,
    Im putting together a dbase which I'd like to auto generate a part number out of roughly 5 fields, they will all be drop down lists.

    eg
    Green
    Large
    Wide
    Hairy
    Cheap



    To give part number GLWHC_001 and incrementally counted up for each occurance of the same combination. I need to keep it independant from the auto record numbering if possible.


    If anyone has any ideas that would be very helpful as I really cant rely on the autonumber because I need to place the part number on the item and visually identify it from that number at a glance..
    Cheers

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You want to take the first initial of each value and append an incremented number for each occurrence? This will require very careful VBA coding.

    Constructing custom unique IDs is a common topic. Search forum and Google.

    Here is one for starters
    https://www.accessforums.net/access/...ers-21361.html
    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
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You could build such a custom identifier, but I think it would be a shortcut with some serious limitations in many cases. If it is an exercise or a test to show that it can be done, then go for it.

    However, if this is part of a "real" application, then fundamental to RDBMS is 1 fact in 1 field. Too often people try to codify things to minimize the number of fields without recognizing potential problems.

    In your example, could you write a description of the field that was to hold these data values.
    Perhaps Green is the color of the part; Large may be the size of a flange or the opening on one end of the part; Wide may represent the spacing between marks or irregularities on the part; Hairy may represent the finish; and Cheap which is a relative term no matter what, may represent the current price of the part- or maybe it represents the cost of production of a category of parts. The fact is that nobody knows what these things represent.
    What happens to this set up if you decide to have Gray parts?
    Or if the Price of material and/or production skyrockets and Cheap no longer represents the part? What if someone decides there will be a new parameter/attribute -eg Weight? Do you rebuild all you identifiers?

    So you could make up the code as June7 said - great exercise. But if you are really doing database design, this is really a non-starter (in my opinion). Define a Part. Define the attributes. Design your Tables and relationships. Don't put too much intelligence in a code - it just could come back to haunt you.

    Good luck with your project.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Agree with orange. Your id concept is problemmatic. I do a unique id that follows pattern: YYYYA-####.

    Examples:
    2011A-0008
    2012A-0008

    As you can see, the sequence starts over every year.
    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
    JMac is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2012
    Posts
    21
    Ive actually taken a nice simple path... i think!

    Ive got my form with the drop down options, created from a separate part number table with look ups for each field.
    Ive then placed a text box in the header and used an expression [] &" " & [] to simply add all the chosen field combinations together to form a part number in the text box.
    Ive not tackled the incremental or unique number that will have to be placed on as the last field, but hopefully that's not too difficult, i can use dont duplicate function to create the uniqueness.

    What I want to do now is get that text box output into the part number table and then the whole lot is easily saved as a new record?
    Or
    should the output of the combined fields not be a text box, should I be using some other function box?
    Thanks

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Orange had a question - What will you do if one part is

    Green
    Large
    Wide
    Hairy
    Cheap

    and another is

    Grey
    Large
    Wide
    Hairy
    Cheap

    They will both be GLWHC.

    Now the color component has just lost meaning. Should the G be decoded as Green or Grey?
    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.

  7. #7
    JMac is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2012
    Posts
    21
    Ahh well its only a generic example, Im actually using the second column of the table with single letter identifiers and its all compiled of drop down lists so there is no confusion possible as I would have it as green with gn and grey gy.. the uniquness will come from a final incremental or non repeatable number.
    So there is definatelty going to be multiple GLWHC parts but the first one logged in will be a GLWHC001 then the next GLWHC002
    I need to get this text box outcome into a table, ideally automatically, but I could be persuaded to copy the text box manually and past it into the main table if its not possible easily???
    Its a little on the confidential side so I cant go into the exact nature of the database...

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Why not design such that you have a field for Access' use to handle relationships/ PK etc and specific one fact one field columns as needed to remove all ambiguity.
    You might even consider designing for new, yet thought of, attributes --- weight, density,smell who knows. But this is the time to design in the flexibility.

    I have experienced too much intended intelligence embedded in a code, and then a new change is forced upon the application, and the design just won't cut the requirement. Build in some flexibility, it will save headaches and refit down the road.

    Good luck , whatever you decide.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you really want this scheme, saving the constructed value requires simple code. Assuming the field is included in the form's RecordSource, in some event (form Close maybe):

    Me!fieldname = Me.textboxname

    The tricky part is generating the value. Common topic, search: unique ID
    Here is another https://www.accessforums.net/access/...ber-20506.html
    Last edited by June7; 02-17-2012 at 03:16 PM.
    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.

  10. #10
    JMac is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2012
    Posts
    21
    Quote Originally Posted by June7 View Post
    If you really want this scheme, saving the constructed value requires simple code. Assuming the field is included in the form's RecordSource, in some event (form Close maybe):

    Me!fieldname = Me.textboxname

    The tricky part is generating the value. Common topic, search: unique ID
    Here is another https://www.accessforums.net/access/...ber-20506.html
    Thanks June7... I think thats pretty much what Im after...
    The current status is...

    2 tables
    Table 1 is presented in a form with 5 drop down list boxes plus an auto number box. These 6 boxes are added together [dropdowna]&"_"&[dropdownb] etc etc into a part number in a text box at the header of the form... this form is only able to be opened in an 'add form from a switchboard button' so the combination of the 5 drop downs plus the incremental auto number will always create a unique id and never be duplicated. Its academic how many records are created in this form as it serves only as a generation tool.

    So thats the first process that the user performs..

    Now as the user is looking at their new part number in the text box i would like to get that number across automatically into Table 2 which contains the more detailed information about the part (I am trying to make it idiot proof to a point)

    So I think the process should be to use your suggested code to get the part number into a field in table 1, and then use a relationship from table 2 to grab the part number and use it in the second process which is to add the more detailed information to the part number and store it all as a new unique record in table 2???

    Thanks.. JMac

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Try a form/subform arrangement. Main form bound to table1, subform bound to table 2. Master/Child link properties will synchronize the related records. Save record with the new part number, refresh the form, enter data into subform. The part number will automatically populate the foreign key field in table 2.
    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.

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

Similar Threads

  1. Replies: 3
    Last Post: 06-23-2011, 07:39 PM
  2. Replies: 1
    Last Post: 04-28-2011, 03:32 PM
  3. generating automatic numbers in a database
    By bonbon68 in forum Access
    Replies: 3
    Last Post: 03-16-2011, 10:09 AM
  4. how do I create custom part numbers?
    By Sinatra Fan in forum Database Design
    Replies: 1
    Last Post: 09-01-2006, 04:09 PM
  5. generating random numbers on form
    By anitra in forum Forms
    Replies: 1
    Last Post: 02-14-2006, 10:08 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