Help with record duplicates!

[Pat Hartman]

The further problem that that created and another error I was getting have disappeared by moving the code to "On Got Focus" event of the "Hours" field. The duplicate record check is performed clear the data then set focus to the first field of the form.

It is important that you use the correct event to do your validation. If you are looking for a duplicate and this is a SINGLE field, you put your DCount() expression in the BeforeUpdate event of the CONTROL where the value is entered. If there is a duplicate, you cancel the event. I rarely delete what the user has typed. It is more user friendly to let him see his typo. You would have to show us the code that was causing the can't go to record error.

If the duplicate is defined by a combination of fields, then the only realistic way to validate is to use the FORM's BeforeUpdate event. The code is essentially the same and you STILL have to CANCEL the event.

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.

 

[swell]

EDITED
In Access 0 is always false. True is typically -1 (but in some situations, True can be any number other than -1). Obviously that excludes 0 and if that's not clear, review the first sentence!
So -1 and 0 are numeric representations of T and F.
Yes/No and On/Off are textual representations of -1 and 0

Micron, Thank you for your reply.
As all fields in my test are tested for the correct values and can not be empty which they were not, and the field that was giving the error is numeric and is never zero or null why was it necessary to make the change? Doing so fixed the error!
Is this something done to overcome a bug in access?

 

[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").

 

[Pat Hartman]

I'll say it one more time. If you are using the OnFocus event to validate data, you are using the WRONG event.