rs.bookmark failing only sometimes

[belovedcej]

I have a lookup screen where, after a search, the user can click on a record to be taken to it.

The relevant code on the double-click of the list view is the following:

g_caseID = Me.listCases.Column(0)
Form_frmCases.Visible = True
Call findCase

g_caseID is a global variable used in the findCase routine. The code behind the routine is the following:

Form_frmCases.Requery
Form_frmCases.Visible = True

'find a case
Dim rs As Recordset
Set rs = Form_frmCases.RecordsetClone
rs.Find "Case_ID = " & g_caseID
Form_frmCases.Bookmark = rs.Bookmark
Set rs = Nothing

the problem - it almost always works. But once in a while, I get an error message saying either BOF or EOF has been reached or the record has been deleted. So then I manually search for the case, and it is there.

This occurs on the users machine, but I cannot duplicate it on mine. It appears to be mostly consistent as to the cases it bombs on, but there is nothing special about the data that I can see, and if it were data related, I would have thought it would bomb on mine, too.

We did check permissions on the SQL side - I have the same exact permissions they do.

A further clue - when on the user's machine, if I sorted the case screen a time or two, then tried this, it worked.

The only things suspicious that I see is that I have set the screen visible twice. But I have a hard time seeing how that would affect anything.

The requery is necessary because this routine is often used after the case or various subforms have been edited.

Any ideas?

 

[cmmrfrds]

Read up on the Recordset Find, FindFirst, and Filter. Hint on an ADO recordset there probably no reason to use Find. Filter is the preferred method and the most versatile. You shouldn't need to clone it either if you use Filter. If you need further help after checking it out then post the code you are using.

 

[belovedcej]

Thanks for the tip - will look into it.

 

[belovedcej]

Okay - I have read up on find and filter.

I first tried the filter out, but the problem with it is that it filters out all records except the one I have moved to. Currently, this UI design has navigation arrows, allowing the user to scroll through the records. I will one day change all of this, but for now, a filter doesn't work because after calling it, there are no additional records.

My understanding is that when things fail, it may be that the recordset is actually on the last record, and I have not specified a find direction, so it doesn't see it. So if I do a rs.moveFirst, I should be ok.

Thanks for the tip - should have read up on it first. :}

 

[cmmrfrds]

If you do a movefirst then don't use the Find since it will requery the database (that is why it is not very useful), although a FindFirst will work on the existing recordset. As for Filter you can always turn the Filter off and it will return the full recordset to you.

Here is an example of creating an in memory recordset and then filtering and turning the filter off.


Public Function FabricatedRS()

Dim rs As ADODB.Recordset
Dim varArray As Variant
Set rs = New ADODB.Recordset

With rs.Fields
.Append "myid", adInteger
.Append "mydesc", adVarChar, 50, adFldIsNullable
End With

varArray = Array("myid", "mydesc")

With rs
.Open
.AddNew varArray, Array(1, "first desc")
.AddNew varArray, Array(2, "second desc")
.AddNew varArray, Array(3, "third desc")
.AddNew varArray, Array(4, "fourth desc")
End With
rs.MoveFirst
While Not rs.EOF
Debug.Print rs!MyID; " "; rs!mydesc
rs.MoveNext
Wend

rs.Sort = "myid DESC"

rs.MoveFirst
While Not rs.EOF
Debug.Print rs!MyID; " "; rs!mydesc
rs.MoveNext
Wend

rs.Filter = "myid = 3"

rs.MoveFirst
While Not rs.EOF
Debug.Print rs!MyID; " "; rs!mydesc
rs.MoveNext
Wend

rs.Filter = adFilterNone

rs.Close
Set rs = Nothing

End Function

 

[belovedcej]

Ok - now I'm confused.

I looked up the available properties and methods for ADO (2.5) in the Microsoft API. Find is a property, and Filter is a method. But FindFirst does not exist.

I attempted to use the filter and then turn it off, but doing that sets the record of the form back at the beginning.

The API clearly states that you have to use MoveFirst before using Find so that if the cursor is on the last record Find won't generate an error. I have no problems using the Find method in any of my other applications.

But outside of that, at this point, I'm almost positive that the code is not my issue. I never get an error on my computer at all. But on the user's computer, I get errors intermittently. And when I signed on to her computer as myself and tried, I got errors. So it has something to do with her computer.

I will check the library references, but I would expect that if that were the problem it would be consistent.

I have programmers telling me at work that if I can't duplicate it on my computer, I can't fix it.

 

[cmmrfrds]

Sorry about that, the findfirst is DAO. I did find a link that talks about recordset cloning and some things to consider.

http://www.trigeminal.com/usenet/usenet022.asp?1033

I wonder if the other PC has a DAO referenced checked and in that case the recordsetclone could be DAO. Probably better to add ADODB.Recordset to you definition.

 

[cmmrfrds]

Here is a piece of code to check the type of the recordset of the Form or modify to check the one you create.

Sub CheckRSType()
Dim rs as Object

Set rs=Forms(0).Recordset
If TypeOf rs Is DAO.Recordset Then
MsgBox "DAO Recordset"
ElseIf TypeOf rs is ADODB.Recordset Then
MsgBox "ADO Recordset"
End If
End Sub

 

[belovedcej]

Thanks - I did fix that shortly after my first post - I realize that can be a problem. I am suspicious if the version matters - I have found the some computers here reference the 2.1 library, and other reference the 2.5. Mine uses 2.1, so I need to check that in hers.

I appreciate the link - that was a helpful read!

 

[belovedcej]

Fixed it - oddly enough.

I just did

Form_frmCases.Find "Case_ID = " & g_caseID

and it worked.

still doesn't explain why it never broke on mine.