Results 1 to 11 of 11

Thread: dlookup issue

  1. #1
    Join Date
    May 2009
    Posts
    21

    Unanswered: dlookup issue

    Hello everyone,

    I am still pretty new to vb and Access so bear with me. I have the following code with the error "You cancelled the previous operation".

    Code:
    MailAll = DLookup("[Email]", "PPR_Table", "[Name_Field] ='" & ME.PPR & "'")
    where:

    MailAll is the string variable to be used in sending out in an email
    [Email] is the text column I am searching for
    PPR_Table is the table I am searching [Email] in
    [Name_Field] is the text column in PPR_Table that I am going to match with
    ME.PPR is the current Table/Query text value on the form

    I am not sure what is wrong. Everything comes from text format so there shouldn't be any issues there. The PPR values come from the Name_Field value in the first place, so it isn't searching for a nonexisting value. Any ideas?

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums

    First off, how are you dim'ing the MailAll variable? Is it explicitly dim'd as a string?

    Also, anytime I use a domain aggregate function (such as dlookup), I always nest it inside the Nz() function just to make sure. So like,
    Code:
    MailAll = Nz(DLookup("[email]", "PPR_Table", "[Name_Field] ='" & ME.PPR & "'"),"NULL")
    Or whatever is a safe-fail string for your particular application.

    If you try to depug.print that function, does it kick out what you're after? If you're not sure what that is (since you say you are new), go into the immediate window (from the VBA section, hit ctrl+g I think), and type the following and hit enter:
    Code:
    degub.print DLookup("[email]", "PPR_Table", "[Name_Field] ='" & ME.PPR & "'")
    If it kicks out what you are expecting, that's a start at least.
    Me.Geek = True

  3. #3
    Join Date
    May 2009
    Posts
    21
    Quote Originally Posted by nckdryr
    Welcome to the forums

    First off, how are you dim'ing the MailAll variable? Is it explicitly dim'd as a string?

    Also, anytime I use a domain aggregate function (such as dlookup), I always nest it inside the Nz() function just to make sure. So like,
    Code:
    MailAll = Nz(DLookup("[email]", "PPR_Table", "[Name_Field] ='" & ME.PPR & "'"),"NULL")
    Or whatever is a safe-fail string for your particular application.

    If you try to depug.print that function, does it kick out what you're after? If you're not sure what that is (since you say you are new), go into the immediate window (from the VBA section, hit ctrl+g I think), and type the following and hit enter:
    Code:
    degub.print DLookup("[email]", "PPR_Table", "[Name_Field] ='" & ME.PPR & "'")
    If it kicks out what you are expecting, that's a start at least.
    I am diming like this

    Code:
    Dim MailAll As String
    I put in
    Code:
    debug.print DLookup("[Email]", "PPR_Table", "[Name_Field] ='" & Me.PPR & "'")
    in the Immediate box and now get "variable not yet created in the context".

    I am even more lost than before.

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    I forgot you can't do a me. in the immediate window, sorry. Can you try again with an example you know should work? Like:

    debug.print DLookup("[email]", "PPR_Table", "[Name_Field] ='PPR Example'")
    Me.Geek = True

  5. #5
    Join Date
    May 2009
    Posts
    21
    Quote Originally Posted by nckdryr
    I forgot you can't do a me. in the immediate window, sorry. Can you try again with an example you know should work? Like:

    debug.print DLookup("[email]", "PPR_Table", "[Name_Field] ='PPR Example'")
    Not a problem

    I now am getting

    Code:
    the object doesn't contain the automation object "Name_field."
    Strange, because the Name Field column does exist in the PPR_Table.

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    A dumb question maybe, but I should probably ask anyways: Are you sure you have the table name and field names spelled 100% correctly?
    Me.Geek = True

  7. #7
    Join Date
    May 2009
    Posts
    21
    Quote Originally Posted by nckdryr
    A dumb question maybe, but I should probably ask anyways: Are you sure you have the table name and field names spelled 100% correctly?
    I am sure I did. This is what everything is called.

    http://img132.imageshack.us/img132/7...sespelling.jpg

  8. #8
    Join Date
    May 2005
    Posts
    1,191
    Have you set all the references yet?
    Me.Geek = True

  9. #9
    Join Date
    May 2009
    Posts
    21
    Well, this is my situation:

    Visual Basic for Applications
    Microsoft Access 11.0 Object Library - Have
    Microsoft DAO 3.6 Object Library - Have
    OLE Automation - Have
    Microsoft Visual Basic For Applications Extensibility 5.3 - Have
    Microsoft ActiveX Data Objects 2.1 Library - Have

  10. #10
    Join Date
    May 2005
    Posts
    1,191
    I'm afraid I'm out of ideas at the moment. I'll keep thinking about it and see if I can think of anything else. Otherwise if anyone else wants to chime in....
    Me.Geek = True

  11. #11
    Join Date
    May 2009
    Posts
    21
    I appriciate the help. I am having to use an existing database and it was quite sloppy. There have been issues before with other things. I am very close to just starting from scratch and setting it up right.

Posting Permissions

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