Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009

    Unanswered: Please help me quickly with this form

    I have two tables that I keep track product sales. Firs table [TblProduct] has these fields. ProdID, ProdName, ProdDate, ProdPrice. and ProdID is the key. The second table [TblProdSales] is where I enter product sales and it has these fields. SaleDate, ProdID, ProdQty, ProdPrice. Also I use form to enter product sales and the form has these fields. SaleDate, ProdID, ProdQty and ProdPrice. ProdPrice in the form I'm using Dlookup and here is where I have the problem =Dlookup("[ProdPrice]","TblProduct","ProdID="&[ProdID]).

    Here is the problem the field ProdPrice some times I'm getting the right price and sometimes it is coming error, also if I enter wrong product and i go up to change the product id the price is not changing. Please note I'm using continuous form if it makes different. Thanks

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    Why do you use DLookup for retrieving the price? It would be easier to build a query that would include the ProdPrice column and base your form on that query as its recordsource.

    If you want to use Dlookup you have to check for Null values both in ProdID and in what DLookup returns. e.g.,
    RetValue = Nz(DLookup("ProdPrice","TblProduct","ProdID="&ProdID), <ErrorValue>)
    If IsNull(DLookup("ProdPrice","TblProduct","ProdID="&ProdID)) Then ' Error
    Have a nice day!

  3. #3
    Join Date
    Nov 2007
    Adelaide, South Australia
    Too true, but still the DLookup method should work... albeit slowly.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Dec 2004
    Madison, WI
    You can also use a combobox to display this info and set it up to show the value needed with criteria in the rowsource query of the combobox to match whatever value it needs to match (or fall between) on the form. Then set the sourceobject = [OtherFieldName]. You'd want to lock this combobox field. It's a different way of doing versus the dlookup but the drawback is that you have to put a cover box over the dropdown part of combobox (or it looks sort of ugly.)

    But I personally usually like ST's method with the dlookup providing the recordset doesn't get into the 100,000's (as ST mentioned where every dlookup will take time to calculate longer and longer as more records are added.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts