Preventing duplicate names in a file

[Bob Richardson]

The primary key to a table is IdNo. There are other tables with one to many
relationships to this table that use the IdNo.

Contact File Annual Data
============ ===========
IdNo-----------------IdNo
LastName Year
FirstName ...
.... more fields
more

I've now decided that I want to prevent the Contact File from having two
records with the same name. There are only 1700 records in the file now,
with no duplicates. If we ever get two different people with the same name,
it's not a problem to have "Smith, John" and "Smith, John 2", or perhaps
"Smith, John J." since the name field is not used for external
communication.

I've added a new "FullName" field, made it indexed with no duplicates
allowed, and filled that field on every record with the appropriate "Last,
First". Then on the BeforeUpdate event, for both the LastName and FirstName
fields, I've inserted FullName = LastName & ", " & FirstName.

All works well, except I'm not happy with the error message that occurs when
the name field is duplicated.

I have two questions:

Is the method I've used the best way to solve this problem? It seems overly
complicated for a standard problem. I tried to use the 3 fields (Idno,
FirstName, LastName) as the primary key, but was thwarted by Access.

Is there an "on error" event where I can have my own message and avoid the
Access error message?

 

[Roger Carlson]

I would create a Unique Index on the combination of LastName and FirstName.
It is much like creating a primary key, but it will allow nulls. Leave your
primary key as it is.

To create a Unique Index, open the table in Design vies and click the
Indexes button (it has a lightning bolt on it). On a new line, give the
index a name (say idxFullName) then in the next column choose LastName.
Now, on the next line, choose FirstName WITHOUT giving that line a name.
Now both fields will make up index idxFullName. Go back to the LastName
line of the index and set the Unique property to Yes.

To trap the error, you can use a form and use the OnError event of the form.

 

[Bob Richardson]

Thanks Roger. Worked perfectly.

I would create a Unique Index on the combination of LastName and FirstName.
It is much like creating a primary key, but it will allow nulls. Leave your
primary key as it is.

To create a Unique Index, open the table in Design vies and click the
Indexes button (it has a lightning bolt on it). On a new line, give the
index a name (say idxFullName) then in the next column choose LastName.
Now, on the next line, choose FirstName WITHOUT giving that line a name.
Now both fields will make up index idxFullName. Go back to the LastName
line of the index and set the Unique property to Yes.

To trap the error, you can use a form and use the OnError event of the form.