Preserving values for Old Records

[jamarogers]

Hi All,


Two steps forward and one back...I have a dilemma of preserving "SellPrice" for existing records in my OrderForm. I change the "CurrentSellPrice" in InventoryForm periodically, to apply to future orders. My OrderForm looks up "CurrentSellPrice" in Inventory and applies that value to the "SellPrice" for the Item on the OrderForm. This works fine for new orders (and old ones, IF I don't review them)! Problem occurs when I look back at an old order and the "DLookUp" triggers, then updates the SellPrice to the NEW CurrentSellPrice. Any ideas on how I could DeActivate ALL bells and whistles, once an OrderForm is committed?

[RuralGuy]

Sell price is one of the situations when we break some of the RDBMS rules. Because of the need to keep the history, that value is a calculated (or looked up) value that is actually stored in a table field.

[jamarogers]

So what's your point? That's what I said...SellPrice is "looked up"! That's my problem...When I change that price in the future (as my costs go up), then it is reflected in my old orders, as I peruse them! Solution????

[RuralGuy]

The solution would depend on HOW you are looking up the price. Is it code? Is it in a query? Can you post the relevant portion?

[Missinglinq]

The point is that you need to store the SellPrice in your underlying Table. You could do something like

If Me.NewRecord Then
 Me.SellPrice = DLookup(...)
End If

or

If Nz(Me.SellPrice, "") = "" Then
 Me.SellPrice = DLookup(...)
End If

Linq ;0)>

[jamarogers]

I enter a PO and the following expression looks up the SalePrice (from Inventory), returns it. This is now my SalePrice for this current order...
=DLookUp("[InvSPrice]","Inventory","[InvName]= Forms![SOs]![SOItems]![SOItNme] and [InvSize]= Forms![SOs]![SOItems]![SOItSize]")
...time progresses and my costs go up and I need to raise my SalePrice. I manually update Inventory SalePrice for this item to a higher value.
Now, IF I peruse my old order (that had the OLD SalePrice), it automatically updates to the NEW SalePrice...I want to preserve the OLD SalePrice
for the existing Orders, BUT I also want to visit them at a future date without having them updated to the CURRENT SalePrice. Hope this makes sense,
as it seems like it would be a common problem...

[RuralGuy]

It is a common problem that is usually solved by just storing the sale price as I've indicated. In your case, maybe you only execute the DLookup() *if* the field does not already have a value.

[jamarogers]

Thanks to all for your input and help here, although you didn't provide a plethora of info.
For others like me who aren't yet able to act on "tidbits" of info; Here's how to do it...
1) Lookup the value you need from a Table and make this field "invisible".
2) Create a 2nd field that is bound to the control you wish to preserve and make it visible.
3) Create some simple code that executes AfterUpdate like, "Me.SOItPrice (visible field) = Me.LUInvSPrice(invisible field)"
4) Assign the code to execute for a control (InventoryName in my case) that uniquely represents the record in question.
This will fetch the SalePrice from InventoryTable, write to the SalePrice field in the form that's bound to the SaleOrderTable, and preserve the price for this Order.
Note that clicking again on the InventoryName (in 4 above) will again update the SalePrice to that currently specifed in the InventoryTable. Thus
you should somehow "LocK" this record AFTER you're through with ALL updates...that's my Next Question

[RuralGuy]

It looks to me like you have no field for the SalePrice in the table as the DLookup() you posted in your Post # 6 appears to be in a query. Do you have a Field in the table for the SalePrice or do you have to look it up every time?

[jamarogers]

You're correct that INITIALLY, I had no field for the SalePrice...it was just looked up and never saved. Now I have added a 2nd field that is bound to the SaleOrderTable.SalePriceField, to which is written the looked up value.

[RuralGuy]

The sequence you described in Post # 8 seems overly complicated and excessive. If you have the SalePrice displayed on the form and bound then when you have enough parameters to determine the value then look it up and put it in the SalePrice Control IF it is a new record (Me.NewRecord = True), otherwise leave the control alone.

[jamarogers]

Thanks for that...I sometimes can't see the forest for the trees. I have updated my (our) solution in Post#8...appreciate your patience and help. Now on to making that SalePrice PERMANENT (in the SaleOrder) so I can't carelessly overwrite, when I update it in Inventory.

[Dal Jeanis]

Another option would be to have a saleprice table that included the effective date of the price, and match up thataway.

However, coming from the accounting side, I prefer to have the actual invoice price stored on the order detail records.