Recordset FindFirst Not Working

[ShoresJohn]

I’ve run across a problem that, after 15 years of Access development, has me stumped. Here’s the situation:

In an OpenForm command, I’m passing the primary key value of the record I want to locate when the form is opened. In the Form_Load I call a subroutine in the form’s code that locates the desired record using RecordsetClone and FindFirst. If the record is found, I set the form’s bookmark to the RecordsetClone bookmark. All of that works just fine. The problem arises when I make a change to the data and close the form. The changes are not saved.

If I use the RecordsetClone and loop through the table to find the desired record, the changes are saved when the form is closed. The problem with doing this is that the search time is prohibitively long.

I’ve used the FindFirst method in many other applications and even elsewhere in this application without any problems. Has anyone encountered this and have you come up with a solution.

The applications uses Access 2007 linked through ODBC to Sql Server 2008 R2 database.

[Fred C]

I had a similar problem when I added a new "search" field when I was trying to resolve my findfirst problem. The search appeared to work correctly, and it displayed the correct information, but SOMETIMES (not everytime), when the user changed the data, and then exited the program or moved to a different record and then came back, the changes were gone. Upon looking into this further, I found that the changes the user made, actually were stored with/under an entirely different record (not good). I had to scrap this new search field and live with my initial findfirst problem. You may want to look at your data and see if your changes actually hit a different record.

[ShoresJohn]

Fred C - great sleuthing. I never thought of the possibility that what I was entering wouldn't update the record I was looking at.

I checked by entering a unique value in a comments field on the form, closed the form and searched the database using a Sql Server Management Studio query. You are absolutely correct. The value showed up in a totally unrelated record. I tried with another record and the same thing happened. Now I have to figure out why.

[ShoresJohn]

I looked at Fred C's post in his own thread about a similar problem and discovered the following:
When I filter my recordset to 24,146 records and do a .FindFirst, I have no problem with updating the wrong record.
When I filter my recordset to 27,212 records and do a .FindFirst, the changes to the record on screen are actually done in a different record.
I can't seem to find any explanation about what the record limit might be and to why it even exists.

[Fred C]

I found something about MaxPageSize and MaxTempTableSize, but after reading about them, I still do not understand if either or both of these could be having something to do with this, or with both of our problems. My problem is not after 1,000 or 10,000 records, it is after 25,600 records (and only the first time I perform a findfirst on them).

[ssanfu]

I remember long ago there was a problem with the .FindFirst command. I found these pages:

http://allenbrowne.com/bug-04.html

http://support.microsoft.com/?id=238134
http://support.microsoft.com/kb/903074/