dlookup mygod !

[krishnanhemanth]

hi all
iam stuck with dlookup again
its pretty plain, but iam not able to get through

i user fosusername() to detrmine the loginname on all forms

i have a table---- tblemployees with the following fields

empid
strempname
emppassword
accesstype
straccessno

i want the straccessno to be displayed on all the forms ( criteria is --- fosusername = strempname)
i put a text box on the form and gave this value

=DLookUp("[straccessno]","tblemployees","[strempname]=" & "'" & [fosusername] & "'")

i tried this also


=DLookUp("[straccessno]","tblemployees","[strempname]=" & "[fosusername]")

it gives me "#Name?"

please help

 

[pbaldy]

See here for the syntax required for a text field (your first effort was correct):

http://www.mvps.org/access/general/gen0018.htm

Also, to reference the function you wouldn't surround it in brackets. That would only be appropriate if it was a field in the data.

 

[krishnanhemanth]

go it !!!! my god !!!

thanks pbaldy for your idea and time

the answer is...

=DLookUp("straccessno","tblemployees","fosusername() = " & "strempname")

it rocking

 

[DCrake]

Are you sure this is working?
The syntax is all wrong

=DLookUp("straccessno","tblemployees","fosusername () = " & "strempname")

straccessno = field name in table
tblemployees = table name
fousername() = is a function that returns the users login name
strempname = the criteria.

So this would translate to

Look in the straccessno field in the table tblemployees where Fred=strtempname


What is should be saying is

=DLookup("StrAccessNo","TblEmployees","StrEmpName='" & foUserName() & "'")

 

[pbaldy]

I was a little surprised too, but it will work:

?dcount("*","tblEmployee", "eeName = fosusername()")
1

The reversal of field and value working didn't surprise me, but the lack of single quotes did.