I am not referring to SET DELETED command. As you might know that when record is deleted from dbf, it not *physically* removed, it is marked as "Deleted". Unique Index doesn't consider this by default. So, you have specify "Filter" condition with that index. You can do this with Table Designer, Click on the Filter button for the unique index and put "!DELETED()" and see if it works.





>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
>>>
>>>
>>>
>>>