Maintain cost after order submitted and price changes?? (1 Viewer)

T. McConnell

Registered User.
Local time
Today, 02:28
Joined
Jun 21, 2019
Messages
63
Sorry for the title, not sure the best way to word it. So my question is for all you experts, (thank you all by the way for everything, ya'll have helped a LOT) I have a two inventory tables with forms (well subforms on a mainform) that have a part cost associated with them, and a selling price. Is there a good way to have these setup as to when an order is created and submitted, that if the cost and selling price changes it won't affect any previous order in the Orders Table? To hopefully explain this better I have part A that I paid $10 for and sell it for $15, I go through this batch of inventory and my new batch I purchased for $12 and sold for $17. How can I manage the inventory that if I edit the price in the inventory table any orders prior to the change isn't affected. Would this be a similar case to doing an inventory transaction type setup, if so any suggestions. I want to be able to maintain the changes of the parts without affecting any previous orders at the same time, tell all this information for reporting purposes. Everything I have now is setup for a static price but was asked if it could be a dynamically changing inventory price. Any help would be greatly appreciated, and I apologize for the confusing post. If you need any other information let me know.
Thanks
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 23:28
Joined
Aug 30, 2003
Messages
36,118
Typically you'd save the selling price as a field in the table recording sales details.
 

T. McConnell

Registered User.
Local time
Today, 02:28
Joined
Jun 21, 2019
Messages
63
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!
 

Attachments

  • StockInventoryTest.zip
    964 KB · Views: 92
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 23:28
Joined
Aug 30, 2003
Messages
36,118
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.

Well, in my view the easiest way to do that is to save the selling price in the order details table along with quantity. It would be simple to populate that field when the user selects a product from the combo using the second method here:

http://www.baldyweb.com/Autofill.htm
 

Users who are viewing this thread

Top Bottom