VB seek fails after addnew on foxpro table

[Anthonynz]

Hi. I have written a process that takes a list of values from a spreadsheet and updates a foxpro table, adding records if they do not exist, or updating them if they do.

I determine if the record exists by a seek command, which works fine. But if my process has previously added a record, it appears that the seek command fails to find the next item even if it does exist.

What am I doing wriong here? - Code is as follows.

Set RsMasterFile = dbsCompany.OpenRecordset(strMasterFile)
RsMasterFile.Index = "ACCOUNTIDX"

'Loop with each strAccountNo - Code not shown for brevity
RsMasterFile.Seek "=", strAccountNo
If RsMasterFile.NoMatch Then
'We have a new Account - Create it
RsMasterFile.AddNew
RsMasterFile("UniqueId") = strUniqueId
Else
'Update the existing record
RsMasterFile.Edit
End If
RsMasterFile.Update
'End of Loop

If I have an existing Item and then a New Item - this code works, updating the existing item and adding the new one.

But if I have a New Item and then an Existing Item - the code does not work, it adds both items as though they are both new items

I presume that the first addnew affects the index or seek somehow, so that the second item is not found even though it exists, but I cannot find anything that warns about this, or gives a work around.

Does anyone know what is happening here and how to fix?

[sidnei]

Maybe...
And I said MAYBE...

According to your description, I think the seek method doesn't position the cursor on the top of your recordset again.

Try changing your code to position on the first record manually:

'Loop with each strAccountNo - Code not shown for brevity
'HERE IS THE MODIFICATION
RSMASTERFILE.MOVEFIRST
RsMasterFile.Seek "=", strAccountNo
If RsMasterFile.NoMatch Then
'We have a new Account - Create it

and says what happens after this change, ok?
I'm curious about what's happening... ;)

Sidnei

[Anthonynz]

Great Idea - But it didnt work - still does not find the existing record.

So I have disabled the .addnew and .update and it shows the "new" and "existing" messages correctly, so it is something to do with executing the .addnew methinks

[sidnei]

Well, I really believed that this could help you...
This way, I don't have a clue what the Seek method already performs on your recordset.

But I would try one more change: use Filter property instead of Seek.
The filter property filters your recordset, letting only the records that match your criteria. If no record matches your criteria, the recordset will be empty (BOF and EOF set to true).
To filter another record, you need to set the Filter property to "" (empty string), refresh the recordset, and set Filter to the new criteria you want.

Initially I think you can do the same thing with Seek, but it is not a property - it is a method -, so maybe it doesn't work as expected.

With your description, testing without the AddNew method and getting the expected result, I consider that may have a uncommon issue between these two methods...
Try using filter (only for test purposes) and watch what happens. Pay attention to the fact you always have to empty the Filter property before a new criteria is specified.

Hope it works...
Sidnei