Northwind

[SimonW]

In Northwind, a DLookUp function on the [order details] subform populates
[unitprice] with the price of the product being ordered. This is to record
the Product price (which can change) at the time of sale. Here's the
question !
Does anyone know of a way of doing this at query level ? That is, not using
a subform to add products to an order, but using only the query that feeds
the form. It's the only hurdle that prevents me from building an order
processing model that uses only tables and queries. TIA, Simon

 

[Klatuu]

This is the sticking point:
record the Product price (which can change) at the time of sale

If it can change, How do you know what it will be?
How will your process know who ordered what?

 

[SimonW]

The Product price can change for subsequant orders. The original price is
saved to [unitprice] in the [order details] table by a DLookUp function on
the [order details subform].

I want to perform this operation in the query that feeds the form i.e.
[order details extended].

Somehow, I need to run a macro or code when the product is added to an
order. This is in a query not a form. Ideally, I'd like to set a table
property validation rule that [Product Price] = [UnitPrice]. But these
fields are in different tables and represent each side in a one to many query.

TIA Simon

This is the sticking point:
record the Product price (which can change) at the time of sale

If it can change, How do you know what it will be?
How will your process know who ordered what?

In Northwind, a DLookUp function on the [order details] subform populates
[unitprice] with the price of the product being ordered. This is to record
the Product price (which can change) at the time of sale. Here's the
question !
Does anyone know of a way of doing this at query level ? That is, not using
a subform to add products to an order, but using only the query that feeds
the form. It's the only hurdle that prevents me from building an order
processing model that uses only tables and queries. TIA, Simon

 

[Klatuu]

Since you are using a query for this, why not just join the product table and
use the unit price from there?

The Product price can change for subsequant orders. The original price is
saved to [unitprice] in the [order details] table by a DLookUp function on
the [order details subform].

I want to perform this operation in the query that feeds the form i.e.
[order details extended].

Somehow, I need to run a macro or code when the product is added to an
order. This is in a query not a form. Ideally, I'd like to set a table
property validation rule that [Product Price] = [UnitPrice]. But these
fields are in different tables and represent each side in a one to many query.

TIA Simon

This is the sticking point:
record the Product price (which can change) at the time of sale

If it can change, How do you know what it will be?
How will your process know who ordered what?

In Northwind, a DLookUp function on the [order details] subform populates
[unitprice] with the price of the product being ordered. This is to record
the Product price (which can change) at the time of sale. Here's the
question !
Does anyone know of a way of doing this at query level ? That is, not using
a subform to add products to an order, but using only the query that feeds
the form. It's the only hurdle that prevents me from building an order
processing model that uses only tables and queries. TIA, Simon