Duplicate Entry Prevention

[bklewis]

I am a beginner to Access but have managed to create a database and a form that I use to input information into the main table. The form allows me to enter information into 9 fields for each record. The first field is set not to allow duplicates, however, I do not know that I have entered a duplicate until I complete the entire record entry and move to a new record. I would like for Access to check the 1st field for duplication as soon as the field is updated, give me a message box stating that there is a duplicate record, and give me an option to display the duplicate record (in the form) in edit mode. If this is something that someone would be willing to help me with I will be happy to provide table/form/field names so that you can use this information in the code and I can better understand the required text.

Thank you!

[RuralGuy]

You can use a DLookup() in the BeforeUpdate event of the control.

[orange]

How do you define duplicate?

[bklewis]

Will the DLookup() function provide the message and a the duplicate record in a form so that I can edit it or will it simply tell me if there is another record with a duplicate field entry?

[bklewis]

How do you define duplicate?

Another record with an exact match in the first field.

[RuralGuy]

Will the DLookup() function provide the message and a the duplicate record in a form so that I can edit it or will it simply tell me if there is another record with a duplicate field entry?

No. You will need to implement that in the code as well.

[orange]

Can you tell us more about your table? What are the fields?
You can use Dlookup or Dcount to find out if another records with a field value already exists.
You could probably do some programming to edit, start over....

[bklewis]

Can you tell us more about your table? What are the fields?
You can use Dlookup or Dcount to find out if another records with a field value already exists.
You could probably do some programming to edit, start over....

There are 9 fields in the table. 4 Text fields, 1 hyperlink, and 4 Yes/No fields. The first field is named "Code". It is a text field consisting of 5 alpha characters and is set to Indexed with no duplicates. This is the field that I would like Access to check the database for duplicate entries and report it to me so that I do not continue entering data in the remaining 8 fields. It would also be nice if Access would automatically display the duplicate record in form view so that I could edit one of the fields with additional information if needed.

[orange]

What does code represent? Are you using this field to identify records?

[bklewis]

The code field represents a unique code (made up of 5 alpha characters) given to each person in the database. You could think of it as a serial number for each person. No two people would have the same number. There is no reasoning behind the value in the code field. It is not in any particular alphabetical order or sequential order at all.

[orange]

Perhaps you should tell us more about WHAT you are doing/planning. It's possible that your tables and/or forms could be set up differently to help you with duplicate prevention.

[bklewis]

I am sorry that this is so difficult and appreciate your help. I have attached my database with an example record so that you can see what I am trying to do. Instead of entering information on a record and tabbing to the next new record and finding out that there is a duplicate alliance code entry already in the database, I would like Access to search for a duplicate after I update the alliance code field in my form, tell me that there is a record with a duplicate code, and pull up the existing record in a form in edit mode so that I may modify the record with the new information.

[orange]

I suggest you add a field called

PersonId Autonumber and make it Primary Key and let Access take care of it.

On your form add a combobox
make the recordsource of the combo
SELECT [tbl_Alliance_Codes].[PersonID], [tbl_Alliance_Codes].[Alliance Code] FROM [tbl_Alliance_Codes] ORDER BY [Alliance Code];

hide the personId and show alliancecodes alphabetically

When you add a new record you can check visually to make sure it isn't in the combo's list

Just my $.02