How do I keep the original value of a field in Access 2010?

15 pts.
Access Database
Access Forms Database
Microsoft Access 2010
Microsoft Access forms
office 2010

I have an inventory with varying prices (which may change weekly).

 I need to compare the current price with the original price.

However, I only want the users to input one price value in a form.

 I've tried to do this with a datamacro and the "old" command, but every time i update the field it updates to the previous price not the origninal one. Any help is appreciated!

Software/Hardware used:
Access 2010

Answer Wiki

Thanks. We'll let you know when a new response is added.

You would need to alter your products table structure to add an additional field (column) which you can store the original price in.

And your form would need to be modified to assign the original value when inserting new records.

Feel free to add more details or ask for clarification or further help if needed.

Discuss This Question: 7  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Vostro123
    Thanks for that, The problem I am having is that I have a 'price' field and a form which allows users to add an item in an inventory. I've created a query which stores the 'old' price as the prices change- but as they change more than once, it writes over the orignial. What i really need to do is somehow copy that 1st price without it updating as the prices change - as you say, 'assign the original value'. Any ideas on how do to that?
    15 pointsBadges:
  • TomLiotta
    Unless you only want OriginalPrice and CurrentPrice, it sounds like you might need to create a related table to keep historical price changes by date. Adding related tables is one of the fundamental advantages of "relational databases". For basic OriginalPrice and CurrentPrice, just adding the appropriate column should be enough. The two values would start out the same. As time went by, only CurrentPrice should ever be changed. Tom
    125,585 pointsBadges:
  • carlosdl
    Vostro123, as mentioned above, if you don't have an additional field for the original price and you just need the original price and the current one, you should add that column. How to do that ? Right-click on the name of he table and click on 'Design View'. You can add the new column there. Then you have to remove the query that is updating the original price, you don't need it. You need to modify your entry form to store the value entered in the 'price' field into the original_price column too, when new records are inserted. How to do it ? You can use the after insert event of the form: -Right-click on your form and select 'Design View' -In the property sheet select the 'Event' tab -To the right of the 'After Insert' event click on the "..." button and select 'Code Builder' -Write a code similar to this (assuming that price is the name of the price field):
    Private Sub Form_AfterInsert()
    original_price = price
    End Sub
    This should be enough to store the original price. Let us know how it goes.
    86,030 pointsBadges:
  • accessprog
    10 pointsBadges:
  • ToddN2000
    There are 2 options. One, you need another column in the table to store the old data or create a second table to store the historical data. If using the 2 table method, you could store multiple old values for data tracking. You could store every weeks value if you found the need.
    136,960 pointsBadges:
  • MAParvana
    I also facing the same problem. Can you please show me how to do that practically?
    10 pointsBadges:
  • ToddN2000
    @MAParvana: post your code you are trying and we will take a look.
    136,960 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: