Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Muse's Avatar
    Muse is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2020
    Location
    TX
    Posts
    15

    Question Consuming a part and linking it to a main record

    Hello, I'm new to this forum and kind of a beginner to MS Access. I've been trying to make a database for work, and I've been researching for over 3 months and can't seem to figure it out. I don't know if this question has been asked before. I've searched but can't find one that is specific to this.



    My dilemma:
    I'm trying to consume a part that needs to be linked to a previous opened form. For example, I have a form called Work Order and in that form I have a button that opens a continuous form that contains records (inventory) that I can choose from. Once I choose a record another form opens that displays that single record. This is where I will input a quantity to be "consumed". What I'm trying to figure out is how do I link this "consumption" to the record that was displayed in the Work Order form?
    The form that displays the single (inventory) record has a command button that will change the quantity in stock but also link the record to the work order, but I can't figure out the latter.

    Any help would be greatly appreciated.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    describing your issue doesn't really help us to help you - what would help us is to see screenshots of the forms and your tables and their relationships.

    For example, why all these forms? why not just a combo on your works order form to list and select a item, together with a control to enter a quantity.

    and comments like this 'a command button that will change the quantity in stock' implies a potential issue with your tables.


  3. #3
    Muse's Avatar
    Muse is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2020
    Location
    TX
    Posts
    15
    I figured I’d explain the issue before posting screenshots and attaching files in case someone might know exactly what my problem is and say “Oh, that’s easy, you just need to do this...” 😅

    With your suggestion of a combo box, I have hundreds of thousands of parts for an inventory and I can’t leave room for error. If someone does not move away from that combo box and accidentally hits a key or bumps the mouse and it scrolls away, there could be some wrongly consumed parts. 😁

    But as I type this, I think I might have another way of going about it.

    Like I said, I’m kind of a beginner so learning to make an efficient database is going to take time for me but thanks for your response.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    After reading the issue, I'm not a whole lot further ahead. I guess that's due to not having as complete a mental picture of the situation compared to someone who has a lot more familiarity with what is going on. Rather than describe what isn't working, describe what you need to do in general/process related terms - not database terms. I gather that there is a work order table and that if the job is executed, parts will be used. IMO that requires at least a tblWO and tblWoParts. If the WO has tasks and the parts go against the task, then you'd need at least
    tblWO
    tblWoTask
    tblWoTaskParts

    if you also want to show stock adjustments because n units are used on a WO or task, then you need a way to relate the WO (or task) to the stock transaction - maybe a quantity adjustment field (+n or -n) and an adjustment type (e.g. WoTask, StockPlus, StockMinus, LoanMinus, LoanPlus, etc). Then again, you might be wanting to show WO parts and those parts come from external suppliers, thus stock transactions doesn't fit your model. Hope that helps put some context into what I'm saying - the process is more important than what you've tried at this point.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    With your suggestion of a combo box, I have hundreds of thousands of parts for an inventory
    a combo can only show 65k records so on it's own that would not work and would not be efficient at that level. But you would have the same problem with your continuous form that contains records (inventory). You might look at cascading combos.

    also link the record to the work order, but I can't figure out the latter.
    this appears to be the nub of your question, but can't be answered without knowing something about your tables and relationships. Best I can suggest is use an update or append query

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Muse,

    Can you provide an overview of the business and major processes in simple, plain English -no database jargon?

    Forms and combos may be part of the solution, but we need a better understanding of the issue/problem/opportunity. You have to ensure your tables and relationships support your business processes.
    Good luck.

  7. #7
    Muse's Avatar
    Muse is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2020
    Location
    TX
    Posts
    15
    Sorry about that. Hopefully, I described it a little better here.

    Click image for larger version. 

Name:	DBase_relation.PNG 
Views:	31 
Size:	26.7 KB 
ID:	43214

    Our company assembles units for a sight system so the database requires us to open up a job order/build order where we can consume parts from inventory.

    What I'm trying to do is open a build-to-order where I can consume multiple parts.

    tblAssy_Inv - where we choose what will be assembled
    tblBTO - the actual job order
    tblParts_Consumed - consumed parts

    I have a table that contains all the stocked parts but I don't think that needs to be in a relationship.

    Did I explain that right? I'm sure I left something out Thanks!
    Attached Thumbnails Attached Thumbnails DBase_relation.PNG  

  8. #8
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    Muse,

    Is something like this what your looking for? The DHR is referring to a Device History Record or in your case a Work Order. This is something I designed for my work and it works very well.


    Click image for larger version. 

Name:	DHR Pic..PNG 
Views:	30 
Size:	49.9 KB 
ID:	43215

    I can go over how it works but essentially there is a table that contains all of the parts and the parent parts and the subform in the attached is a record of the parts needed to create the parent PN.

    Dave

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    bit confused about your terminology. You have works orders, job orders, BTO and DFR - are all these the same thing

    Also you mention a table the lists parts needed - is needed and consumed the same thing?

  10. #10
    Muse's Avatar
    Muse is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2020
    Location
    TX
    Posts
    15
    Quote Originally Posted by Ajax View Post
    bit confused about your terminology. You have works orders, job orders, BTO and DFR - are all these the same thing
    Sorry, yes. They are all the same. Basically a BTO (build to order).


    Also you mention a table the lists parts needed - is needed and consumed the same thing?
    No, not the same thing. I don't see where I said that. I mentioned that I have a parts table (tblMain_Inv). I was just mentioning that I don't think it needs to be linked to another table.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I don't see where I said that.
    post #8
    but essentially there is a table that contains all of the parts and the parent parts and the subform in the attached is a record of the parts needed to create the parent PN.
    So what is a PN?

  12. #12
    Muse's Avatar
    Muse is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2020
    Location
    TX
    Posts
    15
    That's not my post. That's Dave's post. ^^'

    PN is part number.

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    oops

  14. #14
    Muse's Avatar
    Muse is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2020
    Location
    TX
    Posts
    15
    Does anyone have suggestions? Because I'm stuck. Thanks!

  15. #15
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I would, but too confused with your description. I'm unable to tie it back to your relationships and don't understand your relationships. Also you said you had another way of going about it.

    continuous form that contains records (inventory)
    I don't see an inventory table, or a parts table for that matter

    how do I link this "consumption" to the record that was displayed in the Work Order form?
    which is what table?

    from your relationships you appear to already have a link (BTO_ID)

    and is partnumber the same as part_number?

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Linking Subform to Main Form
    By Alex Motilal in forum Forms
    Replies: 1
    Last Post: 03-17-2019, 10:27 PM
  2. Linking to an Oracle Back End Part 2
    By Paul H in forum Programming
    Replies: 3
    Last Post: 01-23-2019, 09:45 AM
  3. Replies: 16
    Last Post: 03-22-2018, 09:27 AM
  4. Replies: 1
    Last Post: 02-23-2015, 07:06 AM
  5. Linking Subform Control to Main form
    By KWarzala in forum Forms
    Replies: 1
    Last Post: 03-13-2010, 08:32 PM

Tags for this Thread

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