Unique Index Violation

[DaBuzz]

Createing a form that I want to prevent dupilcate values.
I have created an index on 3 fields to be primary to prevent duplicate values from being entered.

I want to handle this nicely in a form for data entry that when they select the "Add" button it checks to see if this had a "Uniqueness of index INDEXNAME is violated" error and if so display a warning and put them back into the form. However, On Error doesn't seem to trap this. Is there a way to identify this specific error?

 

[dgrewe]

Why Wait for the user to hit the Add Button. Use the Valid Method of the Field.

llNotValid = indexseek(this.value, .F. , TableName , IndexName)
if llNotValid
=Messagebox(what you want to say)
Endif
return llNotValid

This will not allow the user to leave the test box until a unique value is entered.


David W. Grewe Dave

 

[Mike Lewis]

DaBuzz,

To take advantage of the primary (or candidate) index in this way, it's best to buffer the table. In your Save button, you do a TABLEUPDATE(). If this returns .F., call AERROR() to check the reason. If this indicates that the error code is 1884, this means the uniqueness of the key is violated.

At the point, you should notify the user, and stay in edit mode so they can correct it if they wish.

You should also have a Cancel (or Revert) button, in which you do a TABLEREVERT(). That way, if the user can't fix the problem, they won't be stuck in the form.

By the way, David's solution will also work, but it might not be what you want in user interface terms or for performance reasons. You've got to decide whether it's worth the overhead of doing a seek each time the user passes through the relevant control, and also if you want to trap the user in that control with no way of cancelling.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[opticalman]

DaBuzz

From my experience....
If those 3 fields are lastname, middlename, firstname, you may need to add a fourth field, something like customernumber. Out of 30,000 customers, I have more than a few with the same 3 names.

Jim Rumbaugh

 

[dgrewe]

Actually my Idea has a small flaw in it. (I wrote it going out the door to a meeting I was late for.)

If the valid method returns a true, it goes to the next field. In the case I wrote you would need to return a False if it finds a hit in the index.

so the return line should be
return !llNotValid


David W. Grewe Dave