DLookUp Windows 7 issue

Topic

New Reply

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]”)

Reply | Quote

Replies

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])

Reply | Quote

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))

Reply | Quote

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))

Reply | Quote

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?

Reply | Quote

Yes it does start with an equal sign. I think there are using it as a control on a form….

Reply | Quote

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 ?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote