Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Location
    Houston
    Posts
    2

    Unhappy Unanswered: DLOOKUP Syntax is killin me!!

    Hi,

    I have been looking through your archives hoping to find something that would help with my DLOOKUP situation..... haven't been able to make anything work as of yet.

    I'm trying to build a form in which a user selects a name from a combo box and the form automatically populates the next field(text box) with the corresponding id#. I have tried many variations of syntax and each have resulted in a ... name? or #error! Arrrrg!

    Here's the current syntax (being used in the record source of the text box):

    =DLookUp("[ID]","[IDNUMBER]","[NAME] =" & [Forms]![COMBO47])


    I'm sure this is basic101 stuff... but I'm to the point that nothing makes sense any more. lol Any suggestions would greatly be appreciated. Thanks! Michelle

  2. #2
    Join Date
    Mar 2004
    Posts
    6
    Try This

    =DLookUp("[ID]","[IDNUMBER]","[NAME] =" & [COMBO47])

    Without the "[FORMS]!"

    If it still does not work you might want to use "On Change" Event.

  3. #3
    Join Date
    Feb 2004
    Location
    Canada
    Posts
    133
    Or perhaps:

    =DLookUp("[ID]","[IDNUMBER]","[NAME] = " & Me.COMBO47)

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    =DLookUp("[ID]", "myTableName", "[NAME] = '" & Me.COMBO47 & "'")


    ALSO, make sure that Me.Combo47 is actually bound to the column you want (via the Bound Column property)...test it:

    MsgBox Me.Combo47

    If the Message Box actually shows what you expect then you should be OK.


  5. #5
    Join Date
    Mar 2004
    Location
    Houston
    Posts
    2

    Thumbs up Many Many Thanks!

    I finally had to give up trying to use the DLOOKUP in the record source of the text box but did try your solution for testing the bound combo box and the results worked.

    Then I turned my attention back to the function and took it out of the record source and used it in the event procedure After update... it looked something like this....

    Text12.Value = DLookup("[JNUMBER]", "IDNUMBER", "[NAME] = '" & Me.Combo14 & "'")

    .... and I'm happy to report it works like a charm. Again, many thanks for your speedy reply! I'm one happy camper! =)

  6. #6
    Join Date
    Apr 2004
    Location
    Vermont
    Posts
    2

    Question Re: Many Many Thanks!

    Originally posted by mojeaux
    I finally had to give up trying to use the DLOOKUP in the record source of the text box but did try your solution for testing the bound combo box and the results worked.

    Then I turned my attention back to the function and took it out of the record source and used it in the event procedure After update... it looked something like this....

    Text12.Value = DLookup("[JNUMBER]", "IDNUMBER", "[NAME] = '" & Me.Combo14 & "'")

    .... and I'm happy to report it works like a charm. Again, many thanks for your speedy reply! I'm one happy camper! =)
    I am trying to do something quite similar and also have had no luck entering the DLookup function directly in the Controls data source property. I am now trying your approach. I am reading it that you put the Text12.Value= statement in the afterUpdate property of the Control that you are entering the data in which the function is to use as the lookup value in the dlookup? When I do this, I get an error that Access cannot find a Macro with the name of Text12 (or the equivalent in my project). Am I needing to create a macro here to use this approach? I thought this was a simple statement setting the value of a second control (text12) to the result of a dLookup function based on the value of the control containing the afterUpdate property? Is this making sense to anyone? I too have hit a wall and am lost in trying to get what seems like it should be a simple process to work. When I put my DLookup statement directly in the DataSource of the "Text12" control, I get a null value. Is this because the lookup table needs to be part of the data source for the form? When I do this, the form goes blank. Can anyone help?

    Thanks in advance,
    Frustrated in Vermont,
    Andi

  7. #7
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404

    DLOOKUP - AAARRRGGHHHH!

    DLOOKUP does not cope well with variables. If you want to use a value from a combo box, put the value into a function, e.g:

    Public Sub_SetName(TxtIn as string)
    myName = TxtIn
    end Sub

    Public Function get_name() as String
    get_name=myName
    end function

    ... and in the combo box


    Private Sub comArmyType_Click()
    set_name Me.comArmytype
    End Sub


    in DLOOKUP

    =DLOOKUP("Surname", "nameTable", "Surname=get_name()")

    That's one way of using DLOOKUP with a combo box, list box etc. It works - I use it regularly

    :-|

  8. #8
    Join Date
    Apr 2004
    Location
    Vermont
    Posts
    2

    Arrow Like to avoid VB programming

    Hi Ryker,

    Thanks for your reply.

    While I have had some very (VERY!) limited exposure to writing VB code, I would like to avoid that for the time being. Otherwise I could probably use an array and accomplish what you are suggesting. I barely remember that stuff and so that would be a whole "nother" can of worms.......

    Also, I am going to have 100 controls. 50 CntItem(x) and 50 CntDesc(x). That's why I was hoping to be able to write one SQL statement (using the tool) and just copy and paste replacing the (x) into each new set of controls as I create them.

    Any other thoughts that might not involve VB code?

    Thanks again,

    Andi

  9. #9
    Join Date
    Dec 2012
    Posts
    1
    Quote Originally Posted by Ryker View Post
    DLOOKUP does not cope well with variables. If you want to use a value from a combo box, put the value into a function, e.g:

    Public Sub_SetName(TxtIn as string)
    myName = TxtIn
    end Sub

    Public Function get_name() as String
    get_name=myName
    end function

    ... and in the combo box


    Private Sub comArmyType_Click()
    set_name Me.comArmytype
    End Sub


    in DLOOKUP

    =DLOOKUP("Surname", "nameTable", "Surname=get_name()")

    That's one way of using DLOOKUP with a combo box, list box etc. It works - I use it regularly

    :-|
    Thank you, Ryker. I've searched all over the web for the proper syntax. Yours worked for me.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    DLOOKUP works fine with variables and controls.

    effectively DLOOKUP is a fragment of SQL (I wouldn't be at all surprised if the actual machine code from DLookup is a SQL statement) the Criteria is effectively a WHERE clause without actually using word WHERE

    AS the end result is SQL then you MUST encapsulate text literals with a quote or speech mark
    eg:-
    'MyStringColumn ="' & mystringcombobox.value & '"'
    'MyNumericColumn = ' & mynumericcombobox.value
    'MyDateColumn = #' & format('mm/dd/yy',mydatecombobox.value) & "#"
    dates MUST be in US format
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    DLOOKUP actually uses the Jet engine. You can easily test it:

    1. Activate of the Jet debugger (in Access 2003):
    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Debug]
    "JETSHOWPLAN"="ON"
    2. Create a simple procedure in a module named "Module1" (the is no other user-defined objects in the database):
    Code:
    Sub Test()
    
        Dim x
        x = DLookup("Type", "MSysObjects", "Name='Module1'")
        
    End Sub
    3. After running Sub Test(), this can be found in the file showplan.out that the Jet engine generates when the Registery key "JETSHOWPLAN" is set to ON:
    --- temp query ---

    - Inputs to Query -
    Table 'MSysObjects'
    - End inputs to Query -

    01) Restrict rows of table MSysObjects
    by scanning
    testing expression "Name='Module1'"

    --- temp query ---

    - Inputs to Query -
    Table 'MSysObjects'
    - End inputs to Query -

    01) Restrict rows of table MSysObjects
    by scanning
    testing expression "Name='Module1'"
    Have a nice day!

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Sinndho View Post
    DLOOKUP actually uses the Jet engine.
    thought so
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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