updating data

  • Thread starter Thread starter Valerie
  • Start date Start date
V

Valerie

This may be a stupid question, but as a newbie, I have
lots of those. I hope you'll indulge me anyway.

If I create a purchase order today and the price has
changed since the last time I ordered this item, I key in
the new price. This updates my Parts table. If, I then
pull up a purchase order that I did a few months ago, is
it going to pull up the price I paid then or today's
price?

Obviously, I want to be able to retain the integrity of
old records, but I want to continually update my data.

Thanks in advance,

Valerie
 
This depends on which table has the price information. It
should be on the Purchase Order table in order to preserve
the history. If it is on the Parts table only, then any
change in the price is likely to be reflected in all the
past Purchase Orders.

Hope This Helps
Gerald Stanley MCSD
 
Actually, two tables have the price information at
present -- Inventory Transactions and Parts. Previously,
Inventory Transactions was the recordsource for the
Purchase Orders subform wherein the parts ordered
information was contained. I changed the recordsource to
a query combining the Inventory Transactions table and
the Unit Price from the Parts table.

Ideally, what I want to have happen is for the price to
appear in my PO as soon as I key in the part number. Then
I want to be able to override it with the current price
if different. Obviously, I want the price paid to appear
in the Inventory Transactions table, but I also want it
to update the Unit Price in the Parts table....all
without affecting past history. Is there any way to
accomplish this?
 
To update the price in the parts table, I suggest that you
put code similar to the following in the price text box's
AfterUpdate event handler

DoCmd.RunSQL "UPDATE YourPartsTable SET yourPriceColumn = "
& priceTextBox.Value & " WHERE yourPartCodeColumn = " &
yourPartControl.Value

Obviously, you will have to change YourPartsTable,
yourPriceColumn, priceTextBox and yourPartControl to match
your form and table. If the partCode column on your Parts
table is a text, then you will have to enclose
yourPartControl.Value with single quotes e.g.
yourPartCodeColumn = '" & yourPartControl.Value & "'"

Hope This Helps
Gerald Stanl;ey MCSD
 
That worked! And as one who knows nothing about Code, and
even had to struggle to figure out what the "AfterUpdate
event handler" was, I learned a great deal in this little
exercise.

Thanks so much for your help!

Valerie
 
I do have one more question in regard to this issue.

Each time I enter the unit price in my purchase order, I
am prompted with the following message:
"You are about to update one row. Once you click yes, you
can't use the undo command to reverse the changes. Are
you sure you want to update these records?"

I understand the built-in safeguard. However, I will be
the only one operating this database, and having created
it, I am aware that I will be making a permanent change.
Therefore, I would rather not have this message pop up
each time. Is there any way to remove it?
 
Back
Top