Results 1 to 9 of 9

Thread: DLookup Help

  1. #1
    Join Date
    Jun 2002
    Location
    UK near Blackpool
    Posts
    18

    Unanswered: DLookup Help

    I have a subform in a Invoice database that has a description field that is a Combo Box looking at a table of Invoicable items with the price.

    I have the Combo selecting the correct data ok but cannot get it to transfer the price into the price field.

    This is my current DLookup line being used in a AfterUpdate on the description field.


    Me![UnitPrice] = DLookup("UnitPrice", "OrderItems", "ProductID = " & ProductID)

    This produces a syntax error (missing operator)

    I had tried previously
    Dim strFilter As String
    Me!UnitPrice = DLookup("UnitPrice", "OrderItems", strFilter)

    This worked but ony dragged the price of the first record in the table accross.

    Any ideas appreciated.

    Dave

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sounds like that might be a text field:

    http://www.mvps.org/access/general/gen0018.htm

    In any case, it would probably be more efficient to add the price to the row source of the combo, and get it from there using the column property.
    Paul

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depending on how much data you have (rows in the combo box), or how volatile that data is then a cunning trcik can be to seelct the price as part of the combo box rowsource, nake the prioce ahidden column (width=0) and then retrieve the appropriate column fromt he selected item in the combo box. bear inmnd the combox column(x) satarts from base 0, ie 3rd item in tyou combo box rowsource is column(2).
    I think the selected/isselected collection coudl be you friend here

    However I wouldn't use this approach if there is any risk that the price could change whilcst the form is open, or you have a lot of data. So it may not be appropriate for price data, but it is a cunning trick on certain combo box layouts

    looking at your code
    have you checked what ProductID contains?
    if its an issue then consider assigning the SQL to a variable
    eg:
    strFilter = "ProductID = " & ProductID
    msgbox "strFilter is:" & strFilter
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2002
    Location
    UK near Blackpool
    Posts
    18
    Thanks for the replys, I already have a row source for the combo box on the subform as:

    SELECT OrderItems.Description, OrderItems.UnitPrice FROM OrderItems ORDER BY OrderItems.Description;

    This works fine for the initial opening of the form but doesnt update when the combo looses focus.

    The table only has 3 fileds:
    ProductID (Autonumber & key)
    Description (Text)
    UnitPrice (Currency)

    The control source for the Description field is OrderDetails.Description
    and for UnitPrice OrderDetails.UnitPrice

    this is because the info from this form including totals calculated is saved in a table called OrderDetails.

    Hope this makes sense, I am sure im missing something simple.

    thanks for the help
    Dave

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Me![UnitPrice] = DLookup("UnitPrice", "OrderItems", "ProductID = " & ProductID)
    This might not work but give it a try:
    Code:
    Me![UnitPrice] = DLookup("UnitPrice", "OrderItems", " ' ProductID = " & ProductID " ' ")
    (I think I have the single and double quotes the right way round here... :P)

  6. #6
    Join Date
    Jun 2002
    Location
    UK near Blackpool
    Posts
    18
    Tried that already George but just tried again and get compile error.
    Syntax error

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Apologies, I just lokoed at it again and my suggestion is wrong.
    Been a while since I used DLookups...

    Quote Originally Posted by DaveN
    I had tried previously
    Dim strFilter As String
    Me!UnitPrice = DLookup("UnitPrice", "OrderItems", strFilter)
    in that you never assigned a value to strFilter
    I.e strFilter = ProductID

    Can't think of anything more useful at the moment, sorry

  8. #8
    Join Date
    Nov 2006
    Posts
    40
    Single quote = number
    Double quote = text

    Ex:

    id = '" & ProductID & "'
    id = ' & ProductID '

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by technocraze
    Single quote = number
    Double quote = text

    Ex:

    id = '" & ProductID & "'
    id = ' & ProductID '
    Sorry, but it's single quotes for text and NO quotes for numbers.
    Paul

Posting Permissions

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