Jayesh,

No I don't. The index is a primary index on just citemno.

I'm fairly certain that you have found my problem for me. I didn't realize that attempting to insert a value that was already in the table, but marked as deleted, would cause the error. After I got your post I tested it and realized that VFP will generate the error, regardless of the setting of SET DELETED. I assume this is what you were getting at?

I don't think it is feasible to change the index expression. This code is for a change to Accountmate, a large accounting system with lots of routines that use that index. I don't know what the consequence would be of changing the index.

So I think that I will just SET DELETED OFF while running this routine, then do my seek, and if the seek is successful, then test if the value is deleted, if it is, then I might just rename the deleted key value to something unique, or, not allow the change. Now that I understand the issue I have to think about how to handle it.


Thanks for your help!!!



>Do you have !DELETED() filter on the unique index?
>
>>On the code below, which ran against 600 rows in the curresult table, for one entry, I received the error: Uniqueness of index xitemno will be violated
>>I can't see how this error could happen in this context. Maybe I am overlooking something simple, or there is some simple logic error (I was up late last night!), but I can't see how it could happen unless the seek failed erroneously. This code was running on my test machine, in a standalone environment. I can see how it would happen in a multiuser environment, but not here.
>>(See notes in code below)
>>
>>Thanks in advance for any suggestions.
>>
>>

>>
>>Case alltrim(curResult.tablename)=='icitem'
>>	Select 0
>>	Use (curResult.tablename)
>>
>>	** find the original item
>>	If seek(lcolditemno,'icitem', 'xitemno')
>>
>>		lnrecpointer=recno('icitem')
>>		** see if the new item exists already
>>		If not seek(lcnewitemno,'icitem', 'xitemno')
>>			** if the new item doesn't exist already,
>>			*go back to the original, and
>>			**change the old to the new
>>			Goto lnrecpointer in icitem
>>
>>                        *** got error message here on this replace. I have
>>                        *** looked at this code a million times, and
>>                        ** can not figure out how it could fail
>>                        ** unless the seek failed when it really should not
>>                        **have
>>			Replace icitem.citemno with lcnewitemno in icitem
>>		Else
>>			Wait wind 'Item ' + lcnewitemno +  ;
>>                        ' already exists    in icitem ' + chr(13) + ;
>>				'Item ' + lcolditemno + ' will be ' + ;
>>                         ' replaced  with ' + lcnewitemno + ;
>>				' in transaction detail lines, and ' +
>>                                 lcolditemno + ;
>>				' will be deleted from   the icitem
>>                                 table '   nowait
>>			Goto lnrecpointer in icitem
>>			Delete in icitem
>>		Endif
>>	Else
>>		Wait wind 'Error: original item not found in change list -
>>                skipping ' nowait
>>	Endif
>>	Use in icitem
>>
>>
>>
>>