How can I prevent an ID from being entered twice in my database?

[dk4300]

Hi!
Help! I have a 5 digit customer ID (long integer) field in frmClients (reads from tblClients). It is NOT a primary key because not all customers have one. I need to prevent a user from duplicating the ID in the database. I'm thinking I need to put some code in the 'AfterUpdate' on the ID field, but I don't know what the code should be. I've never had to do this before.

I would really appreciate if someone could let me know what that would be. I'm fine just having a message box pop up that says something like "ID already exists in database. Delete Record?"

Thanks a ton!

[MMcCarthy]

Hi!
Help! I have a 5 digit customer ID (long integer) field in frmClients (reads from tblClients). It is NOT a primary key because not all customers have one. I need to prevent a user from duplicating the ID in the database. I'm thinking I need to put some code in the 'AfterUpdate' on the ID field, but I don't know what the code should be. I've never had to do this before.

I would really appreciate if someone could let me know what that would be. I'm fine just having a message box pop up that says something like "ID already exists in database. Delete Record?"

Thanks a ton!

In the table set the Indexed property of the [customer ID] to Yes (No Duplicates)

[dk4300]

In the table set the Indexed property of the [customer ID] to Yes (No Duplicates)

Thank you! I had originally ruled that out, because I wanted the 'Required' field set to "Yes", which means I needed the ID field to default to 0. Then for the customers that didn't have IDs, it was saying that the 0s were dupes.

After taking another look, maybe I'll set 'Required' to "No", the 'Indexed' to Yes (No duplicates), 'Default' to null, and check to see if it's filled in on the Unload property of the form.
Thank you so much for your time!!!

[MMcCarthy]

Thank you! I had originally ruled that out, because I wanted the 'Required' field set to "Yes", which means I needed the ID field to default to 0. Then for the customers that didn't have IDs, it was saying that the 0s were dupes.

After taking another look, maybe I'll set 'Required' to "No", the 'Indexed' to Yes (No duplicates), 'Default' to null, and check to see if it's filled in on the Unload property of the form.
Thank you so much for your time!!!

You're welcome.