Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Using the code below using the DLookUp function. We are using an access 2003 db and windows xp and this worked fine. We recevied a new PC with Windows 7 and we are having trouble using the DLookUp function on the Windows 7 PC. We can't migrate from Accesss 2003 to 2007 yet so we really need an fix or workaround for this issue. Any thoughts? Thanks....

    =DLookUp("[event_desc]","[event_codes_outcome]","[code_num] = Forms![EIRELCTIRR0A_qry]![CandidatesCPR_Frm]![event_outcome_cde]")

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    When you say: "We are having trouble?" what actually happens? Do you get an error message? or the wrong results? or what?

    What data type is Code_Num ? Is it a number?

    Try this...but this is how I would write it for any version of Access/Windows anyhow.

    =DLookUp("[event_desc]","[event_codes_outcome]","[code_num] = " & Forms![EIRELCTIRR0A_qry]![CandidatesCPR_Frm]![event_outcome_cde])
    Regards
    John



  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by gbanks View Post
    Using the code below using the DLookUp function. We are using an access 2003 db and windows xp and this worked fine. We recevied a new PC with Windows 7 and we are having trouble using the DLookUp function on the Windows 7 PC. We can't migrate from Accesss 2003 to 2007 yet so we really need an fix or workaround for this issue. Any thoughts? Thanks....

    =DLookUp("[event_desc]","[event_codes_outcome]","[code_num] = Forms![EIRELCTIRR0A_qry]![CandidatesCPR_Frm]![event_outcome_cde]")
    Try putting the parameter outside the quotes, like:
    =DLookUp("[event_desc]","[event_codes_outcome]","[code_num] =" & Forms![EIRELCTIRR0A_qry]![CandidatesCPR_Frm]![event_outcome_cde])
    if code_num is numeric, else enclose double quotes around the parameter, viz:
    =DLookUp("[event_desc]","[event_codes_outcome]","[code_num] = " & chr(34) & Forms![EIRELCTIRR0A_qry]![CandidatesCPR_Frm]![event_outcome_cde] & chr(34))

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks John. I tried using the cide below and in the field name I got an error Name#. Did I do something wrong? Thanks..

    DLookUp("[event_desc]","[event_codes_outcome]","[code_num] = " & chr(34) & Forms![EIRELCTIRR0A_qry]![CandidatesCPR_Frm]![event_outcome_cde] & chr(34))

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Where is this code going?

    Is this code part of a VBA procedure? or is it the Control Source (or perhaps Default Value ) of a Control?

    Does your code start with an = sign?
    Regards
    John



  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes it does start with an equal sign. I think there are using it as a control on a form....

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I can't see anything wrong...but it depends on all the names being correct which I can't tell from here.

    Are you sure you need a Lookup? Can you just join the table event_codes_outcome to the query behind the form, then you could access event_desc directly without using a lookup.

    These are hard to debug.

    You could try putting some code in the Oncurrent Event.

    Code:
    Dim strCode as string
    strCode = Forms![EIRELCTIRR0A_qry]![CandidatesCPR_Frm]![event_outcome_cde]
    msgbox strCode

    Are you able to post a sample ?
    Regards
    John



  8. #8
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    You should be able to localise the problem a bit more using the debugging tools within Access VBA. For example break the statement down into pieces and see which part gives the #name error and what the various values are. I'm concerned that you've been advised to alter the existing code, Although it looks suspiciously wrong, if it works on another PC it should be OK.

    What I would look at first is the project references. Open the VBA window and look at the menu item Tools->References. Are any checked references marked as (MISSING)?. This sometimes happens with new PCs because they don't have legacy DLLs from earlier Office installations. If you do have missing references, scroll down the list and see if there's an earlier version available - uncheck the missing reference and check the older one then recompile.

    It would be useful if you could answer a couple of questions - first, are you using a compiled MDE file or a normal MDB? and what version of the MS Access and MS Office object libraries are referenced in the working systems?

    Ian.

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    You should be able to localise the problem a bit more using the debugging tools within Access VBA.
    My impression is that the OP is not actually running any code at the moment..rather there is just an DLookup expression in the Control Source for a control.
    Regards
    John



  10. #10
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    Oops, yes, function embedded in control properties. Caught out by your suggestion of writing an event handler which put the thread into VBA territory. Will take more care! I do agree with you that adding some VBA should indicate where the problem lies. However, OP hasn't answered your key question - what doesn't work about the original?

    So - gbanks - what (doesn't) happen when you use the unmodified app on the new PC? An error message or an incorrect result in the field?

    And - you're adding chr(34) so presumably code_num is a string field? But in your original you're treating it as a number, and it sounds like its a number. What happens if you leave out the chr(34)? Doesn't matter if the other form's field is text, as long as it looks like a number.

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by gbanks View Post
    Using the code below using the DLookUp function. We are using an access 2003 db and windows xp and this worked fine. We recevied a new PC with Windows 7 and we are having trouble using the DLookUp function on the Windows 7 PC. We can't migrate from Accesss 2003 to 2007 yet so we really need an fix or workaround for this issue. Any thoughts? Thanks....

    =DLookUp("[event_desc]","[event_codes_outcome]","[code_num] = Forms![EIRELCTIRR0A_qry]![CandidatesCPR_Frm]![event_outcome_cde]")
    Some assumptions:
    Code_num is numeric
    EIRELCTIRR0A_qry is a mainform
    CandidatesCPR_Frm is a subform control on the mainform
    event_outcome_cde is a control on the subform

    I would write it this way:

    =DLookUp("[event_desc]","[event_codes_outcome]","[code_num] = " & Forms![EIRELCTIRR0A_qry]![CandidatesCPR_Frm].Form![event_outcome_cde])

    Note the use of .Form and note I'm placing the forms!... outside the quotes.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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