>1. Parameter of what table the record is being added to
>
>2. Opens a SYSKEYS table which has
> Table Name to add record
> Tag identifying the primary key (single field, not concatinated field list)
> Next ID available
>
>3. Locate the record for the table needing new record
> If not found, add and default key to 1
>
>4. Uses the table about to receive the new record AGAIN with alias BOGUS1 (example)
>
>5. Set order in "BOGUS1" (example) and go bottom
>
>6. If the current key in the SYSKEYS table is greater than the last record on file, just add one and continue. If the current key in the SYSKEYS is less, force the SYSKEYS with the current "BOGUS1" key value then add one for the new key.
>
>By using a stored procedure and the "default value" of the primary field, this will always be called when adding records and I have never had a duplication problem yet...
>
>Hope this approach works for you.

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?

--lincoln