Check For Duplicates

[access2010]

We have volunteers doing data entry in the office.
Could I please receive a suggestion so that Duplicate entries are not created?
Such as = Stock_Name
Such as = Symbol_Stock

Thank you.
NIcole

 

[Isaac]

Create an index on the table with a specification of uniqueness on those two fields. then create some data validation on the input form so that there's a user-friendly message when they're about to screw up

 

[arnelgp]

see the Form's BeforeUpdate event.

 

[access2010]

see the Form's BeforeUpdate event.

arnelgp, thank you for your code, which W.O.R.K.S.​

The Volunteers, doing data entry this morning have enter N.O. duplicates.

Nicole

 

[arnelgp]

The Volunteers, doing data entry this morning have enter N.O. duplicates.

 

[Pat Hartman]

The code arne provided is correct and is useful to give you an error message but isn't sufficient to protect the integrity of the database. You also need a composite unique index. This will cause the database engine to prevent duplicates from being saved REGARDLESS of what process tries to save them. For example, if you import a file of ticker symbols, the code on the form does you no good. Duplicates can be added to the table. Only defining a unique index on the table itself will prevent the duplicates. The way the index dialog works is you create a new entry with an index name, pick the first field and select the index properties, Then on the next line, leave the name empty so Access knows that this is to be part of the previously named index and pick the second name. Then a third if necessary up to a max of 10 fields. Other RDBMS' allow more columns in indexes than does Access.

 

[access2010]

The code arne provided is correct and is useful to give you an error message but isn't sufficient to protect the integrity of the database. You also need a composite unique index. This will cause the database engine to prevent duplicates from being saved REGARDLESS of what process tries to save them. For example, if you import a file of ticker symbols, the code on the form does you no good. Duplicates can be added to the table. Only defining a unique index on the table itself will prevent the duplicates. The way the index dialog works is you create a new entry with an index name, pick the first field and select the index properties, Then on the next line, leave the name empty so Access knows that this is to be part of the previously named index and pick the second name. Then a third if necessary up to a max of 10 fields. Other RDBMS' allow more columns in indexes than does Access.View attachment 86929

The code arne provided is correct and is useful to give you an error message but isn't sufficient to protect the integrity of the database. You also need a composite unique index. This will cause the database engine to prevent duplicates from being saved REGARDLESS of what process tries to save them. For example, if you import a file of ticker symbols, the code on the form does you no good. Duplicates can be added to the table. Only defining a unique index on the table itself will prevent the duplicates. The way the index dialog works is you create a new entry with an index name, pick the first field and select the index properties, Then on the next line, leave the name empty so Access knows that this is to be part of the previously named index and pick the second name. Then a third if necessary up to a max of 10 fields. Other RDBMS' allow more columns in indexes than does Access.View attachment 86929

Pat, thank you for your suggestion.
With the current Databases that we are using, there are many duplicate records and an Index will not Work.
Starting December 1st we will reconstruct the Data Bases which we are using and at that time an Index will be used.
Nicole.

 

[Pat Hartman]

With the current Databases that we are using, there are many duplicate records and an Index will not Work.

If the code works, the index will work.