Help with record duplicates!

[swell]

It is important that you use the correct event to do your validation. If you are looking for a duplicate and

The event you have settled on is incorrect. If you don't actually cancel the saving of the record, the bad data will get saved unless the database engine raises an error. Understanding which event to use for which purpose is critical in building solid applications.

Pat, thank you for your reply.
I re-read my description how I fixed my problem, it was maybe not very well written but I actually notify the user that this would create a duplicate record then clear the record before changing focus back to the first field.

 

[Deleted member 147267]

the field that was giving the error is numeric and is never zero or null

This is a bit old so perhaps I'm not recalling all the facts, but in the BeforeUpdate event, the field can be null even though the control is not. Which one you're testing can be important. If your field and control names are the same (I don't allow that) Access can sometimes apply your code to one when you're expecting it to be against the other. I forget which one it will "default" to. Also, don't confuse null with an empty string (although I don't think zls was at play here). "Empty" has a specific meaning in Access - it is the state of an object variable - where the variable does not contain an object, so I might refrain from using "empty" to describe the contents of a field, especially since Null and zls look the same.

Lastly, watch out when using compound indexes or fields. While each record has to be unique across those fields. In a 3 field composite, this is allowed (null is not text here. It means the field contains a null):
apple - pear - orange
apple - grape- null
apple - grape - null

It is the one case I'd say that zls needs to be stored in one or more of the fields with missing data. If the last field is optional and the first two are required, zls only needs to be stored in the last field when saving the record. Thus this is not allowed:
apple - grape - zls
apple - grape - zls
because there is already a record that is the same across the 3 fields.
Don't forget that you don't see a zero length string (or "zls").