ADODB Find Problem with Bookmarks

[scking]

I use a new ADODB.Recordset 'Find' method to move to a specific 'Part Number' in form. Occasionally, I will get a 2001 error 'You canceled the previous operation.'. I've bypassed the Before_Update and On_Current code to exclude that from causing the problem. It's an Access 2003 .ADP database connected to SQL Server 2000. The errors seem to occur on exactly the same PNs each time. If I avoided them the app would work fine all the time, but, of course, that is not reasonable. The knowledgebase didn't help and I don't have much 'help' for ADODB. Any help would be greatly appreciated.


Dim rs As New ADODB.Recordset
Dim varBookmark As Variant
Dim strCriteria As String

Set rs = Me.RecordsetClone
strCriteria = "[LRU Part Number]='" & Me.cboFindPN.Value & "'"

rs.Find(Criteria:=strCriteria)
If Not rs.EOF Then
varBookmark = rs.Bookmark
Me.Bookmark = varBookmark
Else
'"Not found"
End If
Set rs = Nothing

---------------------
scking@arinc.com
---------------------

 

[scking]

This problem caused quite a headache. It was not uncovered easily because of an assumption I made about ADODB Find methods. The assumption was that it would not give an error if the item could not be found. The main form allowed the data, approx 300 rows, to be filtered by various categories. The user would then click to display a navigate form which allowed them to select a PN, again by various categories. It would then look up the value in the recordsetclone. What was happening was that these two forms acted independently on the table data. While the main form was filtered by open records the navigation form filled the combobox with those of, lets say the closed category. The Find method was then unable to find the PN in the recordset clone and produced the error. The solution could have taken many forms but I chose to drive the Main form display by setting the filter property based on the selected item in the navigation form.


---------------------
scking@arinc.com
---------------------