Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    thearn is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2021
    Posts
    11

    Calculating On Hand Inventory

    I need to subtract the quantity used from the on hand quantity, I see the proper way to this is through an update query, however this is a simple database and would just like to have the on hand adjusted when a product/part is used to performed the service. below is code that I have been trying to get to work but have not been successful. Any help would be greatly appreciated.



    Public Sub RemoveQty(PartNumber, NumberToRemove)
    'open table and subtract number from QTY field
    Dim MyDB As Database, MySet As DAO.Recordset, SQLString As String
    Dim TotalInStockNow As Integer
    Set MyDB = CurrentDb
    SQLString = "Select * From [yourPartsTable] Where [yourpartnumberField] = " & PartNumber & ";"
    Set MySet = MyDB.OpenRecordset("SQLString")
    MySet.Edit
    TotalInStockNow = MySet.Fields("NumInStock") '<<Remove the period from the field name
    MySet.Fields("NumInStock") = TotalInStockNow - NumberToRemove
    MySet.Update
    MySet.Close
    MyDB.Close

    End Sub
    Attached Files Attached Files
    Last edited by thearn; 01-17-2021 at 11:37 PM. Reason: Adding attachment

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    At the end of all the folders in the primary zip file, your final file, myzip.zip is empty.

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    A foolproof way to keep tabs on stock is to keep the log of all stock movements. An example:
    You have a table where you register items, like
    tblItems: ItemID, ItemType, ItemName, ...
    And you have a table where you register all item movements in single or in several stock like
    tblStockMovements: MovementID, StockID, ItemID, MovementDate, MovementType, ...
    with different movement types for incoming items (purchase, produced, returned, etc), and for outgoing items (sale, used in production, scrapped, etc.), and also for inventory corrections. You can then easily calculate the stock balance at any time moment adding all incoming movement quantities for item up to this time moment, and deleting all outgoing movement quantities for same item in same time period. Plus you can get a lot of another useful info from this log file whenever you need it.

  4. #4
    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
    Further to info received so far, see this article by Allen Browne.

  5. #5
    thearn is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2021
    Posts
    11
    Quote Originally Posted by ArviLaanemets View Post
    A foolproof way to keep tabs on stock is to keep the log of all stock movements. An example:
    You have a table where you register items, like
    tblItems: ItemID, ItemType, ItemName, ...
    And you have a table where you register all item movements in single or in several stock like
    tblStockMovements: MovementID, StockID, ItemID, MovementDate, MovementType, ...
    with different movement types for incoming items (purchase, produced, returned, etc), and for outgoing items (sale, used in production, scrapped, etc.), and also for inventory corrections. You can then easily calculate the stock balance at any time moment adding all incoming movement quantities for item up to this time moment, and deleting all outgoing movement quantities for same item in same time period. Plus you can get a lot of another useful info from this log file whenever you need it.

    That is what I have been learning, however tracking the movement is not needed in this instance, just want a simple solution where I can just deduct the quantity from the on hand quantity and add the quantity for purchases, as most time the quantity will most likely be "0" anyway as they do not really have parts long term only they are usually purchased and used the same day, as needed to perform the service.

    Thanks for your feedback

  6. #6
    thearn is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2021
    Posts
    11
    I have read the Allen Browne article, that is way more complex than is required for what I am doing. I know this is not the recommended way to set this up, however, I need and want to store the quantity on hand, not looking to audit inventory or even do a physical count.

  7. #7
    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
    thearn,

    As davegri said in Post#2, your zip file is empty. Suggest you post a copy of the database so readers can see your issue/set up in context.

    I need and want to store the quantity on hand, not looking to audit inventory or even do a physical count.
    What has been suggested in terms of a transaction table and stock on hand is a tried and true method. But with more info, we can probably assist/advise.

    What if your table stored value is wrong?

  8. #8
    thearn is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2021
    Posts
    11
    Hi all,

    Sorry I took so long to respond, working during the day and doing this at night, I have reposted the database for your review. This project is for a heavy equipment service business, they really do not keep inventory, I guess this will be mostly used as a way to create (Invoices/Service Orders) and purchase orders while sourcing parts for repairs to equipment, the parts are then used immediately to repair the machine, they do not really stock the parts.

    Thanks so much for any help that can be provided

  9. #9
    thearn is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2021
    Posts
    11

    Attachment

    attachment
    for some reason the file will not upload will try again later

  10. #10
    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

  11. #11
    thearn is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2021
    Posts
    11
    It want matter when an error is detected they will just change the Quantity On Hand to the actual Quantity, the will essential just be using the app to print service orders and purchase orders. they are not really tracking inventory. Can not seem to get the file to upload seems to attempt to upload but then it stops no errors just seems to quit working, It is only 47.9 MB. If you have an email I can try to email it to you.

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if the file is access, remove stuff not relevant to your question, compact/repair, then zip. Upload the zipped file

  13. #13
    thearn is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2021
    Posts
    11
    I can not get the file to upload, will try to explain a bit better.
    I have a table called Service Orders and another table called Service Orders Sub
    I have a form - Service Orders that stores customer name, address, city, state, etc. - it has a sub form named Service Orders Sub that stores the fields - part #, description, quantity, price.
    I have a table called Products that stores the fields - part #, description, Quantity On Hand, price.
    when a service Order is created I want the quantity from the Service Order Sub to be subtracted from the, quantity on hand in the products table that will be triggered by a event procedure "after update" on the quantity field when a number is entered.

    I understand that this design is ripe with chances for errors regarding inventory, but that is a risk we are willing to live with. I have been unable to come up with code to get this to work.

  14. #14
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    this design is ripe with chances for errors regarding inventory
    I have been unable to come up with code to get this to work.


    say's it all really. Personally I'm really not prepared to spend time trying to make a poor design work. I'll just say this

    The basic action you require would be to run an update query to update the 'on hand' value with a new value. Perhaps something like

    sqlStr="UPDATE Products SET QuantityOnHand=QuantityOnHand-" & me.Quantity
    currentdb.execute sqlstr

    However not prepared to help solve what to do when a user enters the wrong quantity or perhaps in the wrong order, or 2 users make a change at the same time, etc.

    good luck with your project

  15. #15
    thearn is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2021
    Posts
    11
    Quote Originally Posted by Ajax View Post
    say's it all really. Personally I'm really not prepared to spend time trying to make a poor design work. I'll just say this

    The basic action you require would be to run an update query to update the 'on hand' value with a new value. Perhaps something like

    sqlStr="UPDATE Products SET QuantityOnHand=QuantityOnHand-" & me.Quantity
    currentdb.execute sqlstr

    However not prepared to help solve what to do when a user enters the wrong quantity or perhaps in the wrong order, or 2 users make a change at the same time, etc.

    good luck with your project[/COLOR]

    Thank you Ajax,

    I totally get what you are saying, however in this environment, those issues are really irrelevant as they are not really tracking their inventory, this is mostly an application to print Service Orders. The on-hand inventory will all be "0" except maybe about 20 different products, they are mostly wanting to keep track of Service Orders and what parts were used to perform a certain service. For example an oil change, they will buy the oil and filter and perform the service the same day. They would not have On Hand Inventory as this would be costly to have the filters and different oil types for the many different machine types and brands they service. Does not make economic sense to keep the products in stock as it may be 2 or 3 years before they service that machine again or maybe never, but they do want to know what type of oil was used and what filter was used so they do not have to research the filter Part # again. They will be able to just pull up the machine info and see what filter and oil was used the last time.

    Many thanks for your time and efforts.

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

Similar Threads

  1. Replies: 15
    Last Post: 06-12-2019, 08:39 AM
  2. Calculating Units on Hand
    By Dave14867 in forum Queries
    Replies: 21
    Last Post: 03-15-2018, 06:34 PM
  3. Replies: 2
    Last Post: 10-21-2016, 05:49 AM
  4. Calculating Stock On Hand
    By namu23 in forum Access
    Replies: 11
    Last Post: 12-29-2014, 09:08 AM
  5. calculating On Hand Quantity
    By hbm001 in forum Queries
    Replies: 18
    Last Post: 04-24-2012, 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