make sure text box value is unique 1

[DSburgh]

I have a form where users will enter a new Shipper. While the Material Code is not the primary key, it does need to be unique for each shipper. I want to place code in the AfterUpdate event of the text box to ensure that the value entered does not already exist. Suggestions?

Brian

 

[PHV]

You may consider the DLookUp function.
You may also create an index not allowing duplicates in the Table design view.

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

 

[DSburgh]

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.

 

[PHV]

If Shipper_Material isn't defined as numeric in the Shipper table:
If IsNull(DLookup("Shipper_Material", "Shipper", "Shipper_Material[tt]='"[/tt] & strMaterial & [tt]"'"[/tt])) Then

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

 

[DSburgh]

Works perfect now, I shoulda know that