Greetings all,
First time posting here. I will start by saying hello and thanks to all those I have got help from by visiting posts. I am sadly another newbie into the Access world, I do have a pretty good understanding of things within Access, but am learning new things almost daily it seems. I do not have a real strong programming background but can usually catch on fairly quick (I hope at least)
So to my question
I have a database that uses inventory based on a static cost right now, however when a new batch comes in the price could change. When I change the new price in the inventory table for that part, any previous orders that have the original price in it wants to refresh to the new price, causing an inconsistency with the order details. I have locked the forms and subforms controls (fields) by if a checkbox is checked when pulling the record (a.k.a Job Complete) however if I go back and open this order, no matter what I do, it seems to refresh the subform data (inventory pieces) to the changed price. I can maintain the data on the mainform but if there is a refresh or loading of the record, even in the table level it changes the price. Is there a way that once I submit the record and the checkbox (Job Complete) is checked, then whatever data/text that is in any textbox (calculated or unbound) doesn't change even if the original value changes (price of the part) and the form/table gets refreshed.
I have tried using the allow edits, locking the field, setting the enabled property to no based on if the checkbox is checked etc... Any suggestions would be greatly appreciated.
To give an example of what I have used so far. I have a search box to find a record, if the checkbox is checked then I want to have all the fields to be locked to where the data doesn't change no matter what. But if any value is changed and it is tied to a calculated value it changes anyways. The below code is tied to the search record button tied to the search box, so when an order is opened up, if the checkbox is checked then the order can not be edited, there is more code that disables other controls but I need to understand how to get the value to remain in these fields or any that have a calculated value and the value changes.
Code:
If Me.JobComplete.Value = True Then 'JobComplete is the name of my checkbox on the mainform frmAdminOrders
Forms![frmAdminOrders]![fsubOrderDetails].Form![txtSaleTotal].Locked = True 'These are the control names on the subform that I need the value to stay even if the value changes
Forms![frmAdminOrders]![fsubOrderDetails].Form![ProductCost].Locked = True
Forms![frmAdminOrders]![fsubOrderDetails].Form![Total].Locked = True
Forms![frmAdminOrders]![fsubOrderDetails].Form![SaleTotal].Locked = True
Else
Forms![frmAdminOrders]![fsubOrderDetails].Form![txtSaleTotal].Locked = False
Forms![frmAdminOrders]![fsubOrderDetails].Form![ProductCost].Locked = False
Forms![frmAdminOrders]![fsubOrderDetails].Form![Total].Locked = False
Forms![frmAdminOrders]![fsubOrderDetails].Form![SaleTotal].Locked = False
End If
The controls are linked to a textbox on the mainform that stores the value to place into a bound field for my mainform to show in my table.
I have a good idea of what I want to happen, just not sure how to get it to work right. Eventually I would like to incorporate some type of FIFO stock system or an In and Out style methodology, but that will take some time. Right now I want to just make sure that when the price is changed for the stock I receive in, it doesn't affect any orders with the older price. I have looked on here as well for FIFO but with the setup I am working with I may have to completely redo the database to get that type of system to work. Baby steps as I say.
Thanks again for any and all help that can be given.
Take care