dlookup not working -

[Irshad Alam]

I am trying below to fill a field of a form , the code is as below :


Private Sub SlEcode_AfterUpdate()
Me.SlEDesig = DLookup("[EDesig]", "[EmpNewQ1]",
"[ECode]=[Forms]![SlFormA]![SlEcode]")

Me.SlEDesig = DLookup("[EDesig]", "[EmpNewQ1]", "[ECode]='" &
Forms![SlFormA]![SlEcode] & "'")

End Sub

I tried both the above method, none is working.

Note : EDesig is a text field, ECode is a text field

Kindly correct my above code.

Regards

Irshad

 

[Daniel Pineault]

Assuming your input variable are correct

EDesig -> Table/Query field name
EmpNewQ1 -> Table/Query to search through
ECode -> Table/Query field name to filter on

Me.SlEDesig = DLookup("[EDesig]", "[EmpNewQ1]",
"[ECode]=[Forms]![SlFormA]![SlEcode]")

will not work! You cannot encase your form value within Quotes.

Me.SlEDesig = DLookup("[EDesig]", "[EmpNewQ1]", "[ECode]='" &
Forms![SlFormA]![SlEcode] & "'")

Should work, but I do have a question. What type of value/data type is
Forms![SlFormA]![SlEcode]? (text, number, ...) If it is a number then you'd
have to use

Me.SlEDesig = DLookup("[EDesig]", "[EmpNewQ1]", "[ECode]=" &
Forms![SlFormA]![SlEcode])

The easiest way to work/test Dlookup or any function for that matter is to
test your commands in the vba immediate window. Once you have the proper
synthax working there, then it will be relatively easy to switch it completly
to VBA.

You may also wish to use the Nz() to deal with Null values, etc. in case
DLookUp doesn't return any values...

Nz(DLookUp(...),"YourDefaultValue")
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[John W. Vinson]

I am trying below to fill a field of a form , the code is as below :


Private Sub SlEcode_AfterUpdate()
Me.SlEDesig = DLookup("[EDesig]", "[EmpNewQ1]",
"[ECode]=[Forms]![SlFormA]![SlEcode]")

Me.SlEDesig = DLookup("[EDesig]", "[EmpNewQ1]", "[ECode]='" &
Forms![SlFormA]![SlEcode] & "'")

End Sub

I tried both the above method, none is working.

Note : EDesig is a text field, ECode is a text field

Kindly correct my above code.

Regards

Irshad

If ecode is in fact a Text field (not a lookup field in your table, which
would actually be a concealed Number) your second example should work. What do
you mean by "not working"? Do you get an error message, incorrect result, no
result at all, monitor catches fire?

My guess, based on insufficient information, is that ECode doesn't in fact
contain the value that you're passing in SlEcode.