Help with a simple DLookUP() formula

[spnz]

Hi there I am embrassed asking about this but I really need some help.

I have been trying to teach myself Dlookup with the help of the forum and help but still can't get it to work how I want it to.

What I am trying to do is for the function to look at a table for a value based on a textbox within a form. If the value exists I want to display a msgbox saying the value exists.

This is what I have been trying,

Private Sub Command7_Click()
Dim varX As Variant
If varX = DLookup("txtFileName", "tblImportInformation", "[FileLocation] = txtFileName") = Null Then
MsgBox ("Value is not in table")
Exit Sub
Else
MsgBox ("Value is in table")
End If
End Sub

Please help!! this is driving me nuts!

 

[RuralGuy]

Try:

Private Sub Command7_Click()
Dim Answer As Variant
Answer = DLookup("[FileLocation]", "tblImportInformation", "[FileLocation] = '" & Me.txtFileName & "'")
If IsNull(Answer) Then
   MsgBox "Could not locate [" & Me.txtFileName & "]"
Else
   MsgBox "FOUND [" & Me.txtFileName & "]"
End If
End Sub

 

[spnz]

Great thanks for your help Rural Guy.....Worked like a treat!

 

[RuralGuy]

Thanks for posting back and you are very welcome.