I'm using Access 2003.
I'd like to prevent users from entering a duplicate Claim Number in a field. (That is, if the entered Claim Number is already in the table, block it and throw an error message.)
I know I can prevent that by creating a table index on that field with No Duplicates allowed. However, I'd prefer not to do that for two reasons:
1. There are currently duplicates in the table and I'd like to defer dealing with these dupes. Access won't add the index in this state.
2. My practice is to validate at the form level (for required fields, at least) and I'd like to keep my validation at the form level. If nothing else, it gives me more control over the error messages that are thrown.
So in the BeforeUpdate event of the TextBox control in question, I'd like to execute SQL to count the number of instances in the table of the same string the user just entered in the Claim Number textbox. Then if the count is > 0, I'd like to Cancel the update and throw an error message.
I'd appreciate any help on how to do this. I've tried Googling for this, but don't seem to be finding anything.
Wayne
I'd like to prevent users from entering a duplicate Claim Number in a field. (That is, if the entered Claim Number is already in the table, block it and throw an error message.)
I know I can prevent that by creating a table index on that field with No Duplicates allowed. However, I'd prefer not to do that for two reasons:
1. There are currently duplicates in the table and I'd like to defer dealing with these dupes. Access won't add the index in this state.
2. My practice is to validate at the form level (for required fields, at least) and I'd like to keep my validation at the form level. If nothing else, it gives me more control over the error messages that are thrown.
So in the BeforeUpdate event of the TextBox control in question, I'd like to execute SQL to count the number of instances in the table of the same string the user just entered in the Claim Number textbox. Then if the count is > 0, I'd like to Cancel the update and throw an error message.
I'd appreciate any help on how to do this. I've tried Googling for this, but don't seem to be finding anything.
Wayne
