Get price from history & Update price in history

[spacerocket]

Hi,

I am doing a Purchase Order database where I have a PO form (frm_PO),
subform sfrm_PO in frm_PO which is a datasheet to capture the PO items and a
Price History form (frm_PH).

When the item is selected in the sfrm_PO , I need the Unit Price to appear.
The Unit Price must be the latest Unit Price for the Item in frm_PH based on
the latest date.

If there is no order history for the Item, it will show "0" in sfrm_PO and
the user can enter the Unit Price.

Once the PO form is saved, the Unit Price for all the items will be updated
in the underlying table of frm_PH. How do I do this?

Please help.. I've been looking at all the records in this newsgroup and
tutorials but am still cracking my head. Thanks a lot.

 

[Allen Browne]

Why is there a Price History table? Why not just look up the most recent
price in the purchase order details?

Use the AfterUpdate event procedure of the ProductID combo to look up the
most recent Unit Price for that product. Something like this:

=ELookup("[Unit Price], "[Price History]", "[ProductID] = " &
Nz([ProductID],0), "[OrderDate] Desc")

where the ELookup() function is copied from here:
http://allenbrowne.com/ser-42.html

 

[Klatuu]

A price history table is not at all uncommon in Purchase Order and Sales
Order applications. It will be a smaller table with faster lookup. Also,
detail items can be deleted and prices can be negotiated with individual
vendors. In many cases, it will include a price matrix where different
prices are charged based on volume, vendor, customer, product class, etc.

Why is there a Price History table? Why not just look up the most recent
price in the purchase order details?

Use the AfterUpdate event procedure of the ProductID combo to look up the
most recent Unit Price for that product. Something like this:

=ELookup("[Unit Price], "[Price History]", "[ProductID] = " &
Nz([ProductID],0), "[OrderDate] Desc")

where the ELookup() function is copied from here:
http://allenbrowne.com/ser-42.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am doing a Purchase Order database where I have a PO form (frm_PO),
subform sfrm_PO in frm_PO which is a datasheet to capture the PO items and
a
Price History form (frm_PH).

When the item is selected in the sfrm_PO , I need the Unit Price to
appear.
The Unit Price must be the latest Unit Price for the Item in frm_PH based
on
the latest date.

If there is no order history for the Item, it will show "0" in sfrm_PO and
the user can enter the Unit Price.

Once the PO form is saved, the Unit Price for all the items will be
updated
in the underlying table of frm_PH. How do I do this?

Please help.. I've been looking at all the records in this newsgroup and
tutorials but am still cracking my head. Thanks a lot.