Home > IT Answers > Database > How do I keep the original value of a field in Access 2010?
Vostro123
15 pts.
 How do I keep the original value of a field in Access 2010?
Access 2010, Access Database, Access forms, Access Forms Database, office 2010
Hello,

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
ASKED: November 25, 2010  12:28 AM
UPDATED: November 29, 2010  2:02 PM
RELATED QUESTIONS
Answer Wiki:
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.
Last Wiki Answer Submitted:  November 25, 2010  2:53 pm  by  carlosdl   63,580 pts.
All Answer Wiki Contributors:  carlosdl   63,580 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

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?

Vostro123
 15 pts.
 

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

TomLiotta
 110,105 pts.
 

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.

carlosdl
 63,580 pts.