Unique values in a field

[Tony]

Does anyone know a way to restrict a field so you'll get
an error message if you tab out of the field that you've
entered a duplicate value in?

From the help files, using an index will give you an error
message only when you've completed the record. I need the
error message at the unique field.

Thanks for your help!

Tony

 

[Johnette]

Have you tried using a validation rule? You can set up a
set way that you want data entered in a field. If it's
entered wrong, you should get a message like that. You
can set up validation rules in that field's properties.

- Johnette

 

[John Vinson]

Does anyone know a way to restrict a field so you'll get
an error message if you tab out of the field that you've
entered a duplicate value in?

You can do this on a Form (I don't think there are any usable events
in a table or query datasheet though). You'ld put some VBA code in the
BeforeUpdate event of the textbox into which the data is being
entered, something like:

Private Sub txtMyTextbox_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[fieldname]", "[yourtable]", _
"[fieldname] = '" & Me!txtMyTextbox & "'") Then
MsgBox "This value already is in the table", vbOKOnly
Cancel = True
End If
End Sub