Help with dlookup

[Someone]

Hello

I have a form where I would like to check for the existence within a table
the value entered into a particular field in the form. I cannot index the
field to 'no duplicates' because there are instances where a duplicate may
occur, so this option is not feasible.

My intention is to verify whether the entered value exists in the table and,
if so, flag up a msgbox. I aim to do more, but for the purposes of this
post, I just want to achieve as described. I've not tried dlookup before,
so some assistance would be most welcome. Here are some examples I've
tried:

If DLookup("tblFieldName", "tblName", "txtFieldonForm = """ &
Me![txtFieldonForm] & """") = Me![txtFieldonForm] Then MsgBox

If DLookup("tblFieldName", "tblName", "txtFieldonForm = " &
Me![txtFieldonForm] & "") = Me![txtFieldonForm] Then MsgBox

If DLookup("tblFieldName", "tblName", "txtFieldonForm = " &
Me.txtFieldonForm & "") = Me.txtFieldonForm Then MsgBox

When running the above, I always seem to get a positive result (i.e., the
MsgBox) when I enter the value that was originally entered into the table,
but I do not get a message for any other value in the table (it's a very
small table at the moment - it only has test data in it).

Could someone assist in telling me how the dlookup for this purpose should
be structured so I can get this to work? I've read up on the basic
structure, but I just can't seem to get the additional little bits right!
Having said that, am I using the right method? Is there something else I
should be using?

With many thanks
M

 

[david epsom dot com dot au]

Dlookup returns a value, or NULL.

If statements and boolean expressions don't work the
way you expect when you have null values.

Try something like this:

If not IsNull(Dlookup(...)) then Msgbox

using one of your expressions, or I would write the criteria:

"txtFieldonForm = '" & Me![txtFieldonForm] & "'"

(david)

 

[Van T. Dinh]

Perhaps, DCount() is more appropriate here:

If DCount("tblFieldName", "tblName", "tblFieldName = """ &
Me![txtFieldonForm] & """") > 0 Then
MsgBox ...
End If


provided that tblFieldName is a Text Field.

 

[Someone]

Hi David and Van

After a few pulls of my hair and juggling about of the code, I've eventually
got it to work.

Thanks so much for your input
M