I have a form/subform to input students into classes based on the student employee number. The control is a combo box that actually passes the StudentID and on the NotInList event I have code to open a form and allow input of a new student if a StudentID with a matching employee number is not found in the table.
The problem is that every once in a while the database that assigns employee numbers (another database that I have no control over) will duplicate an employee number if it is no longer being used. Since I am using these employee numbers to select students I don't want duplicate employee numbers, but I have no control over assignment of these numbers. It is five digits long and always starts with a zero. The only way it gets duplicated is if the individual is no longer an employee, so I would like to create VBA code that will cause the following to happen:
Detect if a duplicate employee number is attempted
If so find the old value and edit it so the zero at the start becomes an A
If the A has already been used the it should use B, and then C, and so on....
Then allow the employee number to be assigned to the new record
By the way the employee number is NOT my Primary Key but it is indexed not to allow duplicates right now. Also, the employee number field is a text field so it will alow the zero to change to a letter. I would greatly appreciate any help with this issue, thanks.
The problem is that every once in a while the database that assigns employee numbers (another database that I have no control over) will duplicate an employee number if it is no longer being used. Since I am using these employee numbers to select students I don't want duplicate employee numbers, but I have no control over assignment of these numbers. It is five digits long and always starts with a zero. The only way it gets duplicated is if the individual is no longer an employee, so I would like to create VBA code that will cause the following to happen:
Detect if a duplicate employee number is attempted
If so find the old value and edit it so the zero at the start becomes an A
If the A has already been used the it should use B, and then C, and so on....
Then allow the employee number to be assigned to the new record
By the way the employee number is NOT my Primary Key but it is indexed not to allow duplicates right now. Also, the employee number field is a text field so it will alow the zero to change to a letter. I would greatly appreciate any help with this issue, thanks.
