Help on DLookup 1

[jen1701]

Dear Friends,

I am trying to use Dlookup function to find a value. ie.
varX = DLookup("[TestedKey]", "TestEvent", "[SerialNumber] = " & Forms![frmInput]![SerialNumber])
I get compile error on the above code. I can't find out where is the problem. Please can someone shine some lights on this. Thanks a lot in advance.

Jen

 

[PHV]

I get compile error
Can you please post the whole error message ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jen1701]

Thanks PHV for the quick reply. The error message is:
Run-time error "3075"
Syntax error (missing operator)in query expression '[SerialNumber]=33445'.

'[SerialNumber] = 33445' was input through Forms![frmInput]![SerialNumber])

Thanks for the help.

Jen

 

[willir]

Jen

Where is ...[tt]
varX = DLookup("[TestedKey]", "TestEvent", "[SerialNumber] = " & Forms![frmInput]![SerialNumber])
[/tt]
... located?

If the data source for a form, try...
[tt]
= DLookup("[TestedKey]", "TestEvent", "[SerialNumber] = " & Forms![frmInput]![SerialNumber])
[/tt]

If it is part of some VBA code, how have you defined
varX
Is varX compatible with SerialNumber
Specific, is
- SerialNumber numeric or string
- varX numeric, string or varient

 

[PHV]

TestedKey and SerialNumber are fields of a Table/query named TestEvent ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jen1701]

Thanks Willir and PHV for the replies.
I have a main form for entering SerialNumber. The main form has a subform for entering defects details. After the user entered defects, I have code checking whether this SerialNumber is in TestEvent table using Dlookup in the exit subform event. If the SerialNumber entered in the main form matched the SerialNumber in the TestEvent table, an alert will be triggered.
SerialNumber and TestedKey are 2 fields defined as text in TestEvent table.
So in my exit event, i defined varX as string. I was trying to find the value of TestedKey by matching the SerialNumber ented in the main form to the SerialNumber in the TestEvent. If TestedKey is not null, the Serial Number was tested before.

I am not sure how to use Dlookup function here. I hope I have explained it clearly. Thank you very much for your help.

Jen

 

[PHV]

Try this:
varX = DLookup("[TestedKey]", "TestEvent", "[SerialNumber] = '" & Forms![frmInput]![SerialNumber] & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jen1701]

Hi PHV,

Thanks again. I tried your suggestion it did not work. I still get compile error.

Jen

 

[PHV]

And this ?
varX = DLookup("TestedKey", "TestEvent", "SerialNumber = '" & Forms![frmInput]![SerialNumber] & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jen1701]

Thanks again PHV. I tried
varX = DLookup("TestedKey", "TestEvent", "SerialNumber = '" & Forms![frmInput]![SerialNumber] & "'")

It worked this time. Thank you ever so much for helping me out. I gave you a start for the help.

Jen