Hi Lincoln,

>
>Yes, I use a similar "SYSKEY" approach for generating my primary keys and that works fine. Those fields are never seen by the user and are never changed. My candidate_key field however is seen and can be modified by users. Some come from pre-existing data (Inmate Booking ID No.) so I'm not starting from scratch. What I want to be able to do is allow existing numbers to be used but add any new numbers sequentially (with the first two digits representing the last two digits of the year for a 6 digit number). If a new entry is made which uses a pre-existing # (user would edit the system generated number)I want to be able to ensure that the number doesn't already exist. Can this be done reliably?
>

OK, even with the first two digits being the year, the rest of the digits are going to be sequential, right? That is 970001,970002,980001..etc? If that is the case, then you can use the "SYSKEY" approach but have more than one record for the primary key. For example you could have a KEY98 IDed record with it's own 4-digit number, a KEY97 record, etc.