Update a price

[mikekal]

How can ubdate a price but have the old prices for older records.Im not input yet a field in "Inventory" named Unit cost

 

[June7]

I have not looked at db yet. In general, options are:

1. Save actual price into order detail record.

2. create a record for products table for each revised price for each product and have a yes/no field IsActive or a date/time field for DateActive. Either save record id in order detail or use code to pull correct price based on order date and DateActive.

 

[mikekal]

If you have time take a look in my example.I think i cant do all that you tell me.Im not so good in access

 

[June7]

Example for option 1:

Combobox to select product. Combobox settings:
Name: cbxProduct
RowSource: SELECT ProdID, ProdName, ProdPrice FROM Products ORDER BY ProdName;
ControlSource: Product
BoundColumn: 1
ColumnCount: 3
ColumnWidths: 0;1;0.25

VBA code in combobox AfterUpdate event:
Me!Price = Me.cbxProduct.Column(2)

 

[mikekal]

Than you.I try that

 

[June7]

Problem with option 1 is there is no audit trail. Saved price cannot be verified with history because price has been changed in Products table. No way to verify when price became effective or if the saved value was the price in effect at time and not otherwise altered.

With option 2, save record ID and there is history to verify price. Product combobox for new order record would be restricted to "Active" products.

 

[mikekal]

Its ok .I made it.Thank you all

 

[mikekal]

I make that

Combobox to select product. Combobox settings:
Name: cbxProduct
RowSource: SELECT ProdID, ProdName, ProdPrice FROM Products ORDER BY ProdName;
ControlSource: Product
BoundColumn: 1
ColumnCount: 3
ColumnWidths: 0;1;0.25

VBA code in combobox AfterUpdate event:
Me!Price = Me.cbxProduct.Column(2)

 

[mikekal]

Just i put a field in inventory table for price