Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-12-2019, 04:57 PM   #1
T. McConnell
Newly Registered User
 
Join Date: Jun 2019
Posts: 21
Thanks: 6
Thanked 0 Times in 0 Posts
T. McConnell is on a distinguished road
Maintain cost after order submitted and price changes??

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

T. McConnell is offline   Reply With Quote
Old 07-12-2019, 05:25 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,965
Thanks: 13
Thanked 4,055 Times in 3,991 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Maintain cost after order submitted and price changes??

Typically you'd save the selling price as a field in the table recording sales details.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 07-14-2019, 11:17 AM   #3
T. McConnell
Newly Registered User
 
Join Date: Jun 2019
Posts: 21
Thanks: 6
Thanked 0 Times in 0 Posts
T. McConnell is on a distinguished road
Re: Maintain cost after order submitted and price changes??

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!
Attached Files
File Type: zip StockInventoryTest.zip (964.0 KB, 8 views)


Last edited by T. McConnell; 07-14-2019 at 11:18 AM. Reason: Access Version
T. McConnell is offline   Reply With Quote
Old 07-15-2019, 06:49 AM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,965
Thanks: 13
Thanked 4,055 Times in 3,991 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Maintain cost after order submitted and price changes??

Quote:
Originally Posted by T. McConnell View Post
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
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 07-15-2019, 06:57 AM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,029
Thanks: 40
Thanked 3,578 Times in 3,456 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Maintain cost after order submitted and price changes??

cross posted here with answers, question phrased slightly differently but requires same answers

https://www.accessforums.net/showthread.php?t=77591

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Query to find cost at time of order Cowlers Queries 5 01-27-2014 07:20 AM
Max Date for Price for Order Details tl mike Queries 2 06-06-2008 02:16 PM
Assign a cost/price value toodrop down box nbqw General 5 01-17-2008 01:57 PM
Auto Updating Total Order Cost After Quantity is changed dblaszak Forms 4 10-12-2006 04:50 AM
Update New Price From Order TVReplay99 General 6 12-12-2003 11:47 AM




All times are GMT -8. The time now is 08:24 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World