Results 1 to 6 of 6
  1. #1
    Demerit's Avatar
    Demerit is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2013
    Posts
    125

    Using DLookUp function to search the most updated sales Price

    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.

  2. #2
    Demerit's Avatar
    Demerit is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2013
    Posts
    125
    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

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Demerit's Avatar
    Demerit is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2013
    Posts
    125
    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.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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)
    Last edited by June7; 12-25-2013 at 09:55 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Demerit's Avatar
    Demerit is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2013
    Posts
    125
    Thanks June7 its working.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Item without price, or duplicate price
    By Auto in forum Reports
    Replies: 5
    Last Post: 07-29-2013, 09:46 PM
  2. Replies: 0
    Last Post: 12-13-2012, 03:18 AM
  3. Replies: 1
    Last Post: 12-11-2012, 02:57 PM
  4. Replies: 5
    Last Post: 06-30-2011, 02:24 AM
  5. Need Help for Dlookup function
    By wasim_sono in forum Programming
    Replies: 5
    Last Post: 01-04-2006, 08:18 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums