Update a price (1 Viewer)

[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.

 

[Pat Hartman]

You can't do anything with any tool unless you know what has to be done.

There is no Unit price cost in the inventory table and nothing in the schema is set up to price inventory items differently at different points in time. Also, pricing inventory is very complex and is affected by IRS rules.

Are you sure this sample database is even what you want?

Please explain the business problem you are trying to solve.

 

[mikekal]

Its ok .I made it.Thank you all

 

[Pat Hartman]

1. There is an audit trail. It is in the Order details making it hard to access. But I always use option 2 also which Is always the right choice because otherwise, it is very difficult to put in a price that will be effective tomorrow. Are you going to set your alarm clock for 11:55 so you can try to post the price change at exactly midnight?
2. This option makes it more complicated to price old orders unless you also save the unit price in the order.

 

[Pat Hartman]

Its ok .I made it.Thank you all

Wow, that was fast. What IRS approved method did you choose for valuing your inventory?

 

[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)

 

[Pat Hartman]

You must be using a different database from what you posted since the one you posted doesn't have that table in it.

 

[mikekal]

Just i put a field in inventory table for price