I hate DLookups (1 Viewer)

E

Eileen

Guest
I need to check a number field for duplicate values in previous records on exit of the field. I think this has to be done using a DLookup otherwise the unique value property does not kick in till the user tries to save the record, and the error message means nothing to the user.

Can anyone tell me how to put together this Dlookup, and anything else I have to do? My field is called [RefNo] and the table is [Call Details Table].

Please help
Eileen
 

Kensan

New member
Local time
Today, 15:18
Joined
Apr 21, 2000
Messages
9
This code should work - DLookup returns a variant that is Null if the value cannot be found or the value if it finds it.

Dim varRefNo as variant

varRefNo = DLookup("[RefNo]", "Call Details Table", "[RefNo] = " & [RefNo])

where the first [RefNo] is the field in your table you are looking up and the last [RefNo] is the value in the [RefNo] field in your form.

Note that the above assumes [RefNo] is a number. If it is text then use the following

varRefNo = DLookup("[RefNo]", "Call Details Table", "[RefNo] = '" & [RefNo] & "'")

where the finnal [RefNo] is enclosed by single quotes "'"

You can then check the value of varRefNo

If IsNull(varRefNo) then
'The RefNo doesn't exist in the table
'so do the appropriate action
else
'it does exist in the table
'so do appropriate action
end if

Hope this helps
 

Users who are viewing this thread

Top Bottom