I have seen numerous posts across different sites that point to a FIFO style setup. My only issue with this is on the order form I am using, only the Product and Quantity fields are visible, so all the costs are based off a calculation. My biggest problem I am running into is when all the stock is empty from batch 1, and then I restock with batch 2 if the cost I paid and the cost I sell for are different from batch 1, I would like to make sure that any orders done with batch 1's stock maintains the cost on the record and doesn't update. I have a stock acquisition form for entering products into stock as I get them in (by date) I attempted to add a cost field to at least show what I paid for that inventory on that date. I am not entirely concerned about really using a FIFO style but more just on any order no matter what price I adjust on my inventory, it sticks to the order and doesn't change or update the older orders.
My current setup uses a mainform (frmAdminOrders) with 2 subforms (fsubOrderDetails & fsubGenericOrderDetails) which are the two that can have inventory price adjustments as I get new stock in. The way it works now is I have two separate inventory stock tables (Inventory & Generic Inventory) It currently uses a static cost price. Everything works like I want it to for that part. I uses queries for my subforms to pull in the information.
What I would like to do is keep it the same way, however if there is a way to do an in and out setup or FIFO based off what I currently have. I would like to keep track (for reporting purposes) the cost of basically each part at the price based off the acquisition date. As the part is selected on the subforms on the hidden fields the totals are calculated from the current inventory at the current acquisition dates costs. Once that is depleted then the next batch cost which could be different the cost of that part is reflected. If I could even get it to where I put the stock in the database only when the first batch is depleted, and change the price that would be fine. I just want to make sure that any previous order done with the older price sticks and the order locks in and maintains all the calculated fields (even if it got refreshed somehow).
I have attached my current database, I tried to slim it down some, but wanted to keep all the functions in tact. (Yes I know there is a lot of cleanup I need to do in my coding, and a lot is probably not needed) Also there is a lot of hidden fields on the frmAdminOrders form that is in the footer section, these are used to calculate the totals for the various fields. All I want to really mess with is the best way to manage my inventory and control the costs as it changes with each batch of stock I get in. Any help would be GREATLY appreciated. I am still learning a LOT about Access and VBA etc... so please be gentle when tearing it apart haha. Seriously though thank you all who have helped me get this far even. Any questions please feel free to ask.
Note: Hold shift down when opening the database. If you open it up normally there is a username and PW field (Admin/Admin) and ribbon and navigation pane is hidden. You can pretty much test out what you need to using the bypass method.
Used Access 2016 to create the database
Thanks!