Using DLookUp function to search the most updated sales Price

[Demerit]

In my DB, I have the following tables.

Table Name: “Products”

Product ID (PK)

Product Name

Table Name: “Unit”

UnitID (PK)

UnitName

Table Name: “Price”

PriceID (PK)

UnitID (FK of UnitID)

Product ID (FK of Products table)

Unit Price

Standard Cost

QtyPerUnit

DateOfEntry

I used the tables above to create a query named “Products Extended” with the following fields:

Product ID
Product Name
UnitID
Unit Price
QtyPerUnit
DateOfEntry
Sales Price	([QtyPerUnit]*[Unit Price])
Cost Price	([QtyPerUnit]*[Standard Cost])

Now in preparing the invoice, I use a DlookUp function to Search the “Sales Price” After I must have entered “Product ID” and selected the “UnitID” respectively.

The problem I have is in the function; I want it to look for the Sales price following this order (Let me write the code in SQL for better understanding)


SELECT [Product Details].[Sales Price]
FROM [Products Extended]
WHERE [Products Extended].[Product ID] = Forms.[Invoice Detail].[Product ID] And [Products Extended].UnitID = Forms.[Invoice Details].UnitID And [Products Extended].[DateOfEntry] Is most recent;

Most recent date, I don’t know how to write that in SQL but that is what I want the DLookUp Function to evaluate before returning the “Sales Price”

Thanks in advance for your assistance.

[Demerit]

This is the function i have for now, it is based only on the ProductID, I don,t know how to add the other components:

Function GetSalesPrice(Product_ID As Long, UnitID As Long) As Currency
GetSalesPrice = DLookupNumberWrapper("[Sales Price]", "ProductsExtended", "[ID] = " & Product_ID)
End Function

[June7]

With this arrangement I would not save the ProductID and UnitID in the sales/invoice record, I would save the PriceID, then no DLookup needed.

Also don't think I would bother with a UnitID PK, I would just save the UnitName in Price table.

[Demerit]

One issue is that the user has some right to edit the sales price at the time he is preparing the sales order. That is the user can sell at the price in the system or he can edit the price before saving it in the Invoice Details table. This is so because of the society where the db will be used.

[June7]

Okay, consider this approach.

A multi-column combobox with product ID, Name, UnitPrice, Unit, SalesPrice, PriceID as columns. Code in the AfterUpdate event of combobox:

Me.tbxSalesPrice = Me.cbxProduct.Column(4)

Otherwise maybe:

GetSalesPrice = DLookupNumberWrapper("[Sales Price]", "ProductsExtended", "[ID] = " & Me.Product_ID] & " AND UnitID=" & Me.UnitID)

[Demerit]

Thanks June7 its working.