Prevent from duplicate records

[Ray]

I have a table configured that the ID is indexed with no duplicates.
However, I can input duplicate records via an input form. Can someone
advise me how to prevent duplicate records when I use a form to input data.

Thanks,

Ray

 

[Wayne Morgan]

What do you consider to be a "duplicate record"? If the Id field is indexed
as no duplicates, then you won't be able to get a duplicate in that field,
but data in other fields can still be duplicated.

 

[Ray]

Wayne,

I have a table having 4 fields: one auto ID and three other fields. A
duplicate record is defined as three fields are identical in the same order
or are same in either lower case or upper case. How can I prevent any new
record entry from duplicate record in a data input form? Your advice is
appreciated.

Thanks,

Ray

 

[Wayne Morgan]

Access will ignore the case, so "aaa" and "AAA" will be a duplicate. It is
possible to put a no duplicates index across multiple fields. This will
combine the values of the 3 fields and not allow another record where the
combined value is the same. The problem with this is that to be a duplicate,
it must be and exact duplicate (i.e. no typos). So, if a user mistypes an
entry, the duplicate will still exist.

To set up a multiple field index, open the table in design view, go to
View|Indexes, type in a unique name for the index then the field name for
the first field. On the next two rows, leave the index name blank and just
type in the name of each of the next two fields. Sort as desired. Now, place
the cursor in the first row and at the bottom of the window set Unique to
Yes. Close the window.

 

[Ray]

Wayne,

Thanks for your kind explanation.

I tried to create a multiple field index according to your instructions.
However, I had a barrier that there is a Primarykey ID on the second row
that I could not change, deleted it or moved it. Do I need it to create a
multiple field index or I need to remove the primary key from the table?

Your further guidance is appreciated.

Ray

 

[Wayne Morgan]

The field can have more than one index, although not normally recommended.
So, you can add the primary key field to the multi field index. It will be
listed once on a line by itself as PrimaryKey and once under the multi field
index.