Avoiding duplicate values

[Guest]

I want to avoid entering duplicate values into a field that is not the key to
the table. Am I overlooking a simple way to do this?
I thought I may be able to enter a Validation rule but I would need help
with that.
Any suggestions

 

[Allen Browne]

In table design view, select the field and set its Indexed property (lower
pane) to:
Yes (No Duplicates)

 

[Guest]

If the table does not have any duplicate values now, make a unique index on
the field.

If you are stuck with some duplicate values but just want to exclude new
dupes, you'll have to write a procedure at form level to check for the value
before allowing it to be entered in the table. This will only work when
entering data into a form. If someone is working directly within the table or
a query based on the table, all bets are off. Same goes for importing plus
append and update queries.

Your best bet is to fix any dupes in the table now and create a unique
index. Here's a quick query to check for dupes in one field. Just change all
the PRSN_FRNM_TX to the actual field name and PERS to the table name:

SELECT PRSN_FRNM_TX,
count(PRSN_FRNM_TX)
FROM PERS
GROUP BY PRSN_FRNM_TX
HAVING count(PRSN_FRNM_TX) > 1
ORDER BY 2 DESC ;

 

[Guest]

Allen

I am truly sorry. I must have had some sort of Senior Moment. I havent
been involved in anydesign work for awhile and I guess I should stay away or
I'll have to retrain myself.

 

[Guest]

As Allen says indexing the column uniquely will control this at table level,
which will prevent duplicates regardless of how the data is entered, so
should be done. You can reinforce this at control level, however, by
validating the entry in the control's BeforeUpdate event procedure:

Const ConMESSAGE = "This value already exists in the table."

If Not IsNull(DLookup("MyID", "MyTable", "MyField = """ & Me.MyField &
"""")) Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If

where MyID is the key of the table and MyField is the field you want to
contain only unique values. I've assumed its data type is text, hence the
quotes around the value. This will alert the user as soon as they enter a
value in the control. Its not entirely bullet-proof as in a multi-user
environment another user could simultaneously be entering the same value, and
if both users' records are not yet saved the duplication won't be detected
until an attempt is made to save the record by whichever user is slowest off
the mark in doing so. The index violation would be detected then, however,
so you might want to put some code in the form's Error event procedure to
handle this gracefully rather than presenting the user with the standard
system generated error message. You can find out what the error is by
temporarily putting MsgBox DataErr in the Error event procedure deliberately
triggering it to show the data error number. You can then replace the code
in the Error event procedure with code which tests for that DataErr value and
handles it in a more user friendly manner.

Ken Sheridan
Stafford, England