Apryl said:
How do I create an Access field in which I am notified when a value
is being duplicated. For example: I have 500 customers in an Access
database. When I am entering new customers, I want a message to pop
up when I enter a telephone number that is already being used in
another record so that I do not duplicate records.
There are two ways to approach this, one more restrictive than the
other. The restrictive way is to create a unique index (in the table)
on the field. Then it will not be *possible* to add two records with
the same value in that field, and an error will be raised if you try.
You can trap this error and display your own message using VBA code, or
you can just live with the default error message (which isn't very
helpful unless you're a database geek like me).
My guess is that you would not want to be that restrictive.
The second approach is to do a lookup, in the AfterUpdate event for the
field, to see if there is already another record with that value. If
there is, you can display a message (possibly displaying the ID and name
of the other customer) and allow the user to decide whether to (a) keep
the current record with the duplicate value, (b) enter a different
value, or (c) undo changes to the current record and move to the
matching record. This all requires a certain amount of VBA code.
