I had the same problem and there are a few different solutions.

Solution 1. Your primary key needs to have a filter placed on it. !deleted() (yuck)

Solution 2. Before you determine the highest number in the table SET DELETED OFF add the new record and SET DELETED ON when done (poor design!!!!)

Solution 3. Create a stored procedure that will automatically create a unique key (AutoIncrement). There was a posting on here about storing the key in a table.


e.g. Create a table with Table C(30), nKey I

In the Default portion of the database container e.g. Names.LastName DEFAULT = GetKey("Names")

The stored procedure will:
1. lock the new table
2. find the Names record
3. increment the nKey field (nOldKey = nKey) (nKey = nKey + 1)
4. unlock the file
5. return nOldKey

This is a little complicate to explain but, the MAX(Names.ID) will always be 1 less than the ID stored in the new table.


Solution 3 is the best solution!!!!