Results 1 to 5 of 5

Thread: dlookup help

  1. #1
    Join Date
    Feb 2012
    Posts
    3

    Unanswered: dlookup help

    I have figured that dlookup is the way I need to go...

    I have tried it dozens of ways, no luck, hopefully someone out there can tell me what I am missing.

    Btbl
    autoID...field1...field2...bcode...units...unitpri ce...field4

    BCtbl
    bcodes...price...desc

    my subform (Charges) is linked to Btbl and I am trying dlookup to fill unitprice from price

    can you write the dlookup as an expression?
    in the form, bcode is a combobox, in the afterupdate i tried this...
    unitprice = dlookup("[price]" , "BCtble" , "[bcode]=" & bcode)

    i have also tried it as vba code.. no luck
    i tried putting in a stop in the vba code to see if it is even running, it never stops, so i dont know why in the afterupdate isnt even kicking in.

    i get no error, no ?name, no #error, just a blank field staring at me.

    thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Drop the brackets: they're only necessary when the object names (tables, columns, controls...) contain spaces or non-alphanumeric characters (see: Error message when you use special characters in Access databases and INFO: Microsoft Consulting Services Naming Conventions for Visual Basic).

    2. Where do you use this expression (form module, query, property window)? If it's in the Form module and if unitprice is the name of a textbox and bcode is the name of the combobox, and if both controls are on the same form, try:
    Code:
    Me.unitprice.Value = dlookup("price" , "BCtble" , "bcode=" & Me.bcode.Value)
    Note: If bcode (the column in the table BCtble) is not numeric, use:
    Code:
    Me.unitprice.Value = dlookup("price" , "BCtble" , "bcode='" & Me.bcode.Value & "'")
    Have a nice day!

  3. #3
    Join Date
    Feb 2012
    Posts
    3
    Sinndho..
    1. i dropped the brackets..

    2. what is a form module? vba code?

    i have tried in the property window and vba code, currently it is in the property window.

    and yes, unitprice is a textbox, and bcode is the combobox

    2 questions...
    one.does it matter wether the subform is in datasheet or single form view? i would prefer datasheet, but nothing happens when its in datasheet view. when its in single form view, i get an error.
    . access cant find the object 'Me."

    two.does it matter if uniprice is a textbox from the table? i only want the dlookup to run when entering new records since unitprice can change

    thanks

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by MrBowder View Post
    ... currently it is in the property window... when its in single form view, i get an error.
    . access cant find the object 'Me."
    That's why I wrote: "If it's in the Form module". The use of Me, which is a reference to the current from (i.e. the form associated with the module where the code is run), is not valid in the property windows.

    Quote Originally Posted by MrBowder View Post
    two.does it matter if nitprice is a textbox from the table? i only want the dlookup to run when entering new records since unitprice can change
    I never wrote anything about unitprice being a textbox from the table (tables don't have textboxes anyway), is it a question? If unitprice must only be computed on new records (or when the value of bcode changes, I guess), you can use the expression I provided in the the appropriate event handlers: Form_Current (+ Test for new record) and bcode_AfterUpdate.

    Continuous forms and forms in datasheet view behave differently than forms in standard view. I don't have enough information about the way your database is organized to help you there, although I guess that using a query could possibly be easier in your case.

    Note: The way you type then name of the desired value/control changes in your posts: unitprice, uniprice, unitpri ce. Check that there is no typo in your code.
    Have a nice day!

  5. #5
    Join Date
    Feb 2012
    Posts
    3
    Opps.. sorry about the unitprice.. i checked my db and its all right, its just my typing on here

    I do only want to use dlookup when bcode changes, and bcode is text

    you mentioned about using query.. how would that work?

Tags for this Thread

Posting Permissions

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