I chose to use the DLookUp function because I want to ensure that a unique value has been entered as soon as the user types it in (in the AFterUpdate event). This is the code I use to lookup the value from the Shipper table for the Shipper_Material field.
strMaterial = txt_material.Text
If IsNull(DLookup("[Shipper_Material]", "Shipper", "[Shipper_Material] = " & strMaterial)) Then
MsgBox ("No Record Found.")
Else
MsgBox ("'" & strMaterial & "' is not a unique value.")
End If
I'm not real sure about how to use this function, but I am either getting an error message or it always says "No Record Found" even when I know a duplicate record exists.