Results 1 to 7 of 7
  1. #1
    TMcConnell is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    2

    Lock data in a calculated field after initial value changes?

    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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Locking a control does not lock data. If you don't want data to change then it must be saved into field, not calculated and not 'looked up' via a relationship. The trick is figuring out what event to put code into, something like:

    Me!ProductCost = Me.comboboxname.Column(1)

    The alternative is to create a new record in products table when price changes and set the old price record as 'inactive' to exclude from combobox for new record data entry.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    TMcConnell is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    2
    So here's the crazy thing haha, so when I say calculated I am apparently not quite meaning that and that is my fault. So in my products table I do have a static price that is automatically populating the subforms control that when I select my product from the drop down it fills in the rest of the fields for the prices, I just put in a quantity and the totals calculate. I then have that calculated number which is totalled (sum) passed to a textbox within the subform and then is placed into a textbox on the mainform. I then have some code that places that "static" number if you will from the mainforms textbox to the bound control which is what is recorded in my Jobs table. My issue was that if I changed the price in the products table because new inventory came in with a different price then the orders price would change. The really weird part is, literally about 10 minutes after I posted this, I actually went into each one of the controls and manually set them to Locked, and it seems to have maintained the value not only on the form but in the table as well... Could be a fluke but I am waiting to see if there is any issues that arise. I will look into what you were saying about the "inactive" price, I kind of understand what you are saying but a little confused at the same time. But I do appreciate you taking your time to reply back, so thank you for the information. If I run into any crazy issues I will then work on a version I can upload for viewing and suggestions. Thanks again!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Locking a bound textbox merely prevents user editing its content. Does not assure the value cannot be changed by other means.

    So you are saving the unit price? Post your code.

    Exactly what are you showing on the main form - the sum of all detail records in the subform? Calculated totals should not be saved. Save the raw data then calculate totals when needed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Either:
    1. You have table e.g. tblPriceHistory: PHID, ProductID, ProductPrice, ValidFrom, and a function which returns a valid price from this table for certain product at certain date. All forms and reports read product price only from tblPriceHistory;
    2. You have current price for every product stored in products table. And you have a sales/purchases transaction log, where product price at entry date is read from products table by BeforeUpdate event and is stored into hidden/locked price control. Log entries must always be for current date (date when entry was made) and they must be locked for editing after they are saved. All forms and reports read price only from transaction log.

  6. #6
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    TMcConnell,

    I am working on a similar issue in a new project I am working on now. The way I am planning on approaching it is this way. I have a form and subform for Purchase orders, When a new PO is entered and the Part Numbers are entered into the PO, new records are created in a transaction table which includes the price for the each item. When the parts arrive most are give a lot number. When they get used in product, the lot number is recorded. I plan on somehow (query) looking up the lot numbers used and getting the price from the PO they were purchased on, does that make sense? That is the approach I am currently working on. I hope this may be something that may help.

    Dave

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    "Lookup" is best accomplished with a relationship - create a record for each price change and save that record ID in transaction - 'deactivate' old price records. Otherwise save the price in effect at time of transaction.

    Disadvantage of saving price and changing Products table price is there really is no 'paper trail' in db to support the price in effect at time of transaction.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Change Field Name from All Caps to Initial Cap
    By bo16tx in forum Database Design
    Replies: 4
    Last Post: 01-24-2018, 10:31 AM
  2. My calculated field data type is not working
    By Access_Novice in forum Access
    Replies: 1
    Last Post: 11-09-2013, 05:00 AM
  3. Lock a field after data has been entered
    By winterh in forum Forms
    Replies: 8
    Last Post: 03-15-2012, 03:32 AM
  4. Replies: 4
    Last Post: 01-23-2011, 09:01 PM
  5. Code for data import with calculated field
    By Tyork in forum Programming
    Replies: 2
    Last Post: 01-21-2011, 03:15 PM

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