Validation Rule or somthing

[sps2]

Sometimes I just feel stupid.

I have a number field, "EmployeeClock", that is indexed in the table with "Yes (No Duplicates)" turned on. So in the form what I want is when someone tries to edit the "EmployeeClock" number and that number is already being used in that table in that field they will get a message or validation text informing them this Clock Number is already assigned.

However, if you edit an "EmployeeClock" number and move to another field nothing happens.

If they have changed a "EployeeClock" number and move to another record they get a default message explain that they have a duplicated value you but it doesn't tell them which field. If they close the form no message, it just does not save the change.

I've tried using DLookup variations but I can figure it out.

I attached the problem, please help. Thanks

 

[RuralGuy]

Check for duplicates in the BeforeUpdate event of the control that displays the EmployeeClock. Set Cancel = True if it is a duplicate.