Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    honey2wood is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    25

    Update product parts

    I am building an inventory database for parts that go into producing a product.
    I have a Product table, a Parts table, and goods in /goods out table. I also have a 'Transaction query' that takes goods in and subtracts goods out to produce a stock level.
    This is all very basic and works ok.
    I also have a Table that lists the individual parts that make up each product. Using this I have a form that when I select a product from a combo box it gives me a list of parts in a sub form.
    My problem is at the moment as I sell a product I use the goods in/out table and 'Goods Out' the amount of each part to leave the stock level balance. Again this works OK. BUT some of the products have 20 parts and it takes ages to do this for each part everytime I sell a product.


    What I want is a way to enter the Product name with the number sold that automatically reduces all the parts totals.
    I have 8 different products each using some of the same and some individual parts which complicates it a bit. Reject parts can be dealt with individually.
    I expect this has been asked for many times before but I have been trying to find a thread but not sure what to search for.
    Any information will be gratefully received.
    Graham
    Thanks Graham

  2. #2
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Very Interesting problem I must say if I have understood correctly. Each Product is made up of individual parts. When a product is sold all the parts in it will be reduced in quantity. If this is what u are trying to do there are some questions that I have:

    1) How u relate products with Parts.
    2) Is it possible that more that one unit of a part is used to make a product.
    3) If yes how is this allocation made.
    4) Can parts be individually be taken out.
    5) If yes how do you deal with it.

    Can u upload a sample of what u have done so far.

  3. #3
    honey2wood is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    25
    Hi Maximus
    Thanks for getting back to me. Sorry I have been so long replying but I have not had time to visit the site.
    To answer your questions:
    1) How u relate products with Parts.
    I have a BOM table that links the two
    2) Is it possible that more that one unit of a part is used to make a product.
    Not at the moment but may be in the future.
    4) Can parts be individually be taken out.
    Yes but only manually
    5) If yes how do you deal with it
    Enter number manually

    I have attached the sample database.
    I have made it a little smaller
    Many thanks
    Graham
    Last edited by honey2wood; 12-08-2010 at 01:54 PM. Reason: Add file

  4. #4
    honey2wood is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    25
    Hi,
    Does anyone else have an idea on this please.

    Honey2wood

  5. #5
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Graham,

    Quote Originally Posted by honey2wood View Post
    I expect this has been asked for many times before but I have been trying to find a thread but not sure what to search for.
    In 7 years reading over 100,000 posts and posting over 25,000 replies I have only seen this ask maybe 5 times. That would explain why you are not finding anything on this.

    What you are wanting to do is definitely possible to do. I do it in my own software.

    Took a peek at your sample and spotted a red flag for a design issue.

    See: Inventory Control: Quantity on Hand

    Adding BOM/Explosive Kits usually require a fair amount of programming skills to write the code required. I use VBA code to explode a single product into many product line items including quanties based on a product that is a kit.

    This might help: Bill Of Materials

  6. #6
    honey2wood is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    25
    Hi HiTechCoach,
    Sorry but I am a bit confused. I can't seem to get my head around this.
    I am not sure that I know what you mean about the 'BOM / Explosive kit'. How can I improve on what I already have by adding VBcode. I have 'exploded' each product in table BOM that gives the parts per product. The only thing I did not put in is the number of each part required to make a product. In this case it is only one of each.
    What I want is a way to click a button when I enter the number of products ordered and make an entry in the parts Movement table for each part with the number required. In Example 3rd December 2010 3 product 2 is ordered which means part2, 4 and 6 are reduced by 3.
    I am most grateful that you have taken the time to help me I just don't get it.
    Graham

  7. #7
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Graham,

    See:
    The Ten Commandments of Access

    I took your example and fixed some of the the issues with the #2 and #3 commandments. I also added a button for you that might be what you want.

    In the attached is a 2007 version. I also included a 2000 version.





  8. #8
    honey2wood is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    25

    Thumbs up

    Hi HiTechCoach,

    That is great. It works just a s I needed. Now I need to look and understand how it works. I may come back to you when I work it out.
    I want to understand so that I can use it elsewhere. Its no good just copying what someone else has done. You will never learn anything.
    Ten commandments make sense. I have not seen that before.

    Once again Many thanks

    Graham

  9. #9
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Graham,

    You're welcome.

    Did you notice that I added the OderID to the Parts movement table. I did this so you could add a button to reomve the items posted with the Add button I created.

    Good luck with your projct ...

  10. #10
    honey2wood is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    25
    Hi Hitechcoach.

    Sorry its been so long for me to reply. I have been looking at the solution you gave me and I have been trying to adapt it slightly.
    At the moment when the 'Add to Parts' button on the form is pressed the Partmovement date, The Part ID, and Order ID are updated with the entries made. At the moment it enters a 1 in the partmovement Out field. I can follow the code to that point and have been trying to adapt it to enter the 'amount' value instead of 1 but however I do it I get errors. I am sure it is a simple step and I have tried so many different ways but it just wont do it.
    Can I ask you or someone to have another look at it please.

    Many thanks
    Graham

  11. #11
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Graham

    Quote Originally Posted by honey2wood View Post
    Sorry its been so long for me to reply.
    No problem. Still here.

    Quote Originally Posted by honey2wood View Post
    At the moment it enters a 1 in the partmovement Out field. I can follow the code to that point and have been trying to adapt it to enter the 'amount' value instead of 1 but however I do it I get errors..
    I don't follow. What do you mean by enter the 'amount' value instead of 1?

    The code adds all the records to the table with the default amount of 1. After that the user can edit the data to their needs.

  12. #12
    honey2wood is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    25
    Hi Hitechcoach,
    Sorry for not explaining properly.
    On the form the Order amount is the number of products in that order. This means that if a product is made of 3 parts and the order is for 5 products then 5 of each part needs to come off of the stock amount (PartMovement_Parts_Out) to make up that order. As it is what ever the order number only 1 of each part is taken out.
    I have tried to replace the 1 in the code with the Order_amount but I have errors coming up which ever way I try it.
    Thanks again for your help with this.
    Graham

  13. #13
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Would you please post a current copy of your database with what you're attempting to do.

  14. #14
    honey2wood is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    25
    Hi Again Hitechcoach

    I have attached the database I have been trying to change.

    What I need is to Enter a date, a product code and an amount of that product on the Order Form. IF I enter 5 products i need to have 5 of each of the parts that make up the product taken off the partsmovement table. This happens by the amount moving to the parts_out field.
    At the moment whatever the number of products entered only 1 of each part is is entered into the table. All the rest of the code etc works.

    I hope I have explained it ok.

    Many thanks
    Graham
    Last edited by honey2wood; 04-14-2011 at 01:19 AM.

  15. #15
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    In the SQL Insert statement change this:

    ... , 1 AS PO FROM ...

    to

    ... , " & Me.Amount & " AS PO FROM ...

    Just like how the other fields and controld were handled

    Will look like this:

    Code:
      strSQL = "INSERT INTO PartsMovement (PartMovement_OrderID, PartMovement_PartID, PartMovement_Date, PartMovement_Parts_Out) "
      strSQL = strSQL & "SELECT " & Me.txtOrderID & " as OrderID  , BOM.BOM_PartID, #" & Me.txtOrder_Date & "# AS PMDate, " & Me.Amount & "AS PO FROM BOM WHERE (((BOM.BOM_ProductID)= " & Me.cboProduct & " ));"

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

Similar Threads

  1. Accessing different parts of the same form
    By Lxmanager in forum Forms
    Replies: 1
    Last Post: 10-21-2010, 04:52 PM
  2. Product / Stock Management
    By dale.90 in forum Access
    Replies: 1
    Last Post: 09-30-2010, 11:08 AM
  3. field parts
    By DavidAlan in forum Queries
    Replies: 3
    Last Post: 09-07-2010, 01:36 PM
  4. Find all parts in a heirarchy
    By Spiftacu1ar in forum Queries
    Replies: 1
    Last Post: 08-04-2009, 09:01 AM
  5. Product in a Summary Query
    By Fletch in forum Queries
    Replies: 0
    Last Post: 12-11-2008, 03: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