recordset.clone findfirst not working correctly ONLY the first time

[Fred C]

Hello, we have an access application (quote maintenance forms and a SQL database) that we developed and have been using for approximately 9 years. At this point in time, the main/parent file has 25,781 records in it. Until recently, everything was fine. On the form we have a "goto quote" field to allow the user to position them at the quote that they wish to work with. The first time you open the form and use the "goto quote" number field, if a quote number is entered that is really a record number in the file greater than 25,600 - the findfirst displays an arbitray record that is in the group of records from 1 thru 25,600 (not quote number, but a record that is in the first 25,600 records in the file). Second and subsequent use of the "goto quote" field, using the same findfirst processing, takes you to the CORRECT, desired quote number. This is driving me crazy. Is there some limitation to the number of records that are in a recordset at one time or something. I put in a message box for the condition of nomatch, but I'm not getting a message saying it cannot find the record, it just shows the wrong record. Below is my code. Any help with this would really be appreciated. I also tried adding a whole new "goto quote" field in the form header, and that was working, but then changes that users would make to the data would SOMETIMES not be saved (similar to the other gentleman's post today in regard to findfirst).

Private Sub goto_quote_AfterUpdate()
' Find the record that matches the control.


On Error GoTo ProcError
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 3, , acMenuVer70
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[quote_ID] = " & str(Me![goto_quote])
If rs.nomatch Then
MsgBox "Record Not Found"
Else
Me.Bookmark = rs.Bookmark
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
ExitProc:
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & ". " & Err.description
Resume ExitProc
End Sub

Thanks,
Fred

[RuralGuy]

I'm sorry but can you convert the DoCmd.DoMenuItem commands to the new DoCmd.RunCommand format?

[Fred C]

I switched the docmd.domenuitem commands as you requested:

Private Sub goto_quote_AfterUpdate()
' Find the record that matches the control.
On Error GoTo ProcError
runcommand accmdremovefiltersort
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[quote_ID] = " & str(Me![goto_quote])
If rs.nomatch Then
MsgBox "Record Not Found"
Else
Me.Bookmark = rs.Bookmark
End If
runcommand accmdrefresh
ExitProc:
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & ". " & Err.description
Resume ExitProc
End Sub

Thanks,
Fred

[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. 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).

[ShoresJohn]

I think I found a work-around for the the .FindFirst problem. I was experiencing the same thing with a recordset of over 28K records. Try the following:

Dim rs as Recordset
Set rs = Me.RecordsetClone
rs.MoveLast
rs.MoveFirst
rs.FindFirst <search argument>
If not rs.NoMatch then...

[Fred C]

I believe that this resolved my problem, but I will not know for 100% sure until I put it into production at the end of today. I will post the results. Thanks John, you have no idea how much time I have spent on this problem.

[AbSoLuT]

Hello everyone,
I encountered the same problem yesterday (I have around 25670 records) and indeed the rs.MoveLast- rs.MoveFirst works fine. I was wondering if anyone has figured out why this problem is occurring?

THANKS

[Micron]

Kind of an old thread to be resurrecting. The answer was alluded to in post 5.
Regardless, if you've ever opened a Select query on a large set of records and then scroll down, you might notice that you reach a point where scrolling hangs momentarily; you might even see the cursor change to indicate the application is busy. This is because the query is returning the next section/set of records, otherwise the query would take too long to run. Also, if all the records won't fit into memory, the overflow is stored on disk. So either way the Find method may fail if the searched value isn't found the first time. AFAIK, the Find method will cause the rest of the recordset to load.

It is far better to use a sorted query with criteria to limit the records returned to a recordset rather than loading all of them.