Dlookup problem

[Shaun3]

Hi All,

I am a complete newb with Access 2007 (or any access) and am having problems writing a dlookup function. heres the whole story:

Form1 is bound to table TSDETAIL. On form1, I have an unbound combo box (called COMBO0) where the user selects EMPID from table EMPLOYEE. I also have an unbound textbox which I want to display field FNAME from table EMPLOYEE where the EMPID equals the one selected in the combo box. In the expression builder for the Control Source of the text box i inserted the following formula:

=DLookUp([EMPLOYEE]![FNAME],"EMPLOYEE",[EMPID]=[Combo0])

but obviously something is wrong. I can't figure out the right syntax for this seemingly simple problem for the life of me, and have tried searching countless forums without success. Any/All help will be greatly appreciated, and will be repaid with my undying gratitude (although a shiny lamborghini would be more fitting, unfortunately thats all I have to offer)

-Shaun

 

[KenHigg]

DLookUp("[FNAME]","EMPLOYEE","[EMPID]=" & [Combo0])

???

 

[Shaun3]

wow thank you, its working perfectly of course. I know I do feel stupid, but its not as straightforward with 0 expression writing experience. I can't tell you how many variations of little symbols I've tried. Thanks much again.

 

[tokoloshi]

DLookUp takes 3 parameters. The first is the name of the field as a text value - in your case "FNAME". The second parameter is the table or query name - in your case "EMPLOYEE". The 3rd parameter is a criteria to search for within the table or query. This often trips one up because you may have to trim your results and do some conversions. Not always the case, but is becomes good practise to do this outside of the DLOOKUP. In your case you would have done the following:

dim SrchCriteria, someValue
SrchCriteria="EMPID=" & [Combo0]
someValue=DLOOKUP("FNAME","EMPLOYEE",SrchCriteria)

Sean

 

[KenHigg]

Glad to help!

 

[Simon_MT]

A better method of writing your form based Dlookups is:

=DLookUp("[VAT Desc]","[Vats]","[Vats].[Vat] = " & [Forms]![Originals Entry]![Orig Supplier Vat Code])

Note the reference to the Form, change [Orig Supplier Vat Code] and the Dlookup field changes.

Simon