I have a procedure to generate sequential unique values for a field used as a Candidate key when new records are added to my parent table. It works fine when new records are added as long as a new record hasn't been deleted. My procedure searches the table for matches in the candidate field; if it doesn't find a match it increments by one and returns the value to the field. If a new record was just added and then deleted and you try to add another new record an error message indicates a Unique_ID violation. I understand if the record were recalled instead of packed it would indeed be a violation of uniqueness and thus why I'm getting the error message. It seems my procedure doesn't compare the return value with deleted records (deleted but not yet packed).

How can I remedy this situation?

TIA

--lincoln