Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DLookup failure in Access 2003

    A section of an Access 2003 VBA project spontaneously stopped working. After some trouble-shooting, I isolated the problem to a DLookup function. Then operating from the Immediate Window, I found that DLookup was not working as expected. For example, this worked properly:

    ?dlookup("CityID","atblcities","CityName='100 Mile House')

    but this returned an error message:
    ?dlookup("CityID","atblcities","CityName='100 Mile House' and ProvState=2")

    The VBA error message was:
    Runtime error 2001
    You cancelled the previous operation.


    I've been using DLookup for years, and I am sure that the syntax is correct. Any idea what has changed?
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Mea culpa - a typo in a fieldname explains the problem.
    Also, DLookup fails "quietly" if a fieldname is entered incorrectly. Rather than crashing in VBA, or giving a meaningful error message in the Immediate Window, it just returns an incorrect value, and carries on...
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Glad you found the error .
    Rui
    -------
    R4

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by jacksonmacd View Post
    Mea culpa - a typo in a fieldname explains the problem.
    Also, DLookup fails "quietly" if a fieldname is entered incorrectly. Rather than crashing in VBA, or giving a meaningful error message in the Immediate Window, it just returns an incorrect value, and carries on...
    That's one of the reasons I avoid using DLookup in code, and for that matter in most forms. I prefer to use DAO or ADO to do the deed - if I get it wrong I get an error message that I can debug from.
    Wendell

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Wokingham, England
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I can recommend Allen Browne's ELookup as a permanent replacement for DLookup. With the same syntax it can simply be dropped into your existing code/forms and it does report errors. http://allenbrowne.com/ser-42.html

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Dang... what a way to get recognition in the Windows Secrets newsletter. Oh well...
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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