FindNext Error

What's the error?

I get at least two different errors from time to time (Intermittent):
Error 3219 Object Invalid or no longer set
Error ???? Invalid Operation

From  Help on (DAO) Move:

"If either the BOF or EOF property is True and you attempt to use the Move method without a valid bookmark, a run-time error occurs."

I suspect this is the case that is occurring ...

mx

What happens if you change

rs.Bookmark = Me.Bookmark 'Synchronize

to


Me.Bookmark = rs.Bookmark 'Synchronize

?

Are you positive the error is occurring on the FindNext line ?

mx

Hi mx,
<"If either the BOF or EOF property is True and you attempt to use the Move method without a valid bookmark, a run-time error occurs.">
The error is occurring with the FindNext method, not the Move method.

<What happens if you change
rs.Bookmark = Me.Bookmark 'Synchronize
to
Me.Bookmark = rs.Bookmark 'Synchronize>
This would set the current record of the form to the current record of rs, which is initially the first record of rs. This is not what I want.

<Are you positive the error is occurring on the FindNext line ?>
Absolutely.

Another error that sometimes comes up:
Error 3252 Cannot open a form where the underlying query contains a user defined function that attempts to set or get the form's RecordsetClone property.

Err 3252 .... I have never ever seen that happen.  wow.

'Criteria'

Where is the rest of the code?  Where / when does this code get executed ?

mx

Hi mx,
It gets pretty involved (and I don't expect to you analyze my entire database), but what this code is supposed to do is determine if the current record of a form meets given Criteria (which is a string in the form of a Where clause). Is there another another way to do this (that will work whether or not the recordset has a unique index)?

When/How does this code get executed ?

Here is the code I use in some of my Find buttons.  It saves the value of the search string and bRepeat from one call of the subroutine to the next.  It uses the FindFirst method the first time a search is conducted, and FindNext method for subsequent searchs for the same value.  If the search criteria is not found, it will display a message which is case sensitive to the FindFirst or FindNext method.

This particular version searches for records that are Like a search string, not =.

 

Private Sub cmd_Find_Click()

    Static strFind As String
    Static bRepeat As Boolean
    Dim rs As DAO.Recordset
    Dim strCriteria As String
    
    Set rs = Me.RecordsetClone
    rs.Bookmark = Me.Bookmark
    
    If strFind <> Me.txt_Find Then
        strFind = Me.txt_Find
        bRepeat = False
    End If
    strCriteria = "cstr([lngNumber]) Like ""*" & Me.txt_Find & "*"""
    
    If bRepeat = False Then
        rs.FindFirst strCriteria
        bRepeat = True
        If rs.NoMatch Then
            MsgBox "Not found"
            bRepeat = False
        Else
            Me.Bookmark = rs.Bookmark
        End If
    Else
        rs.FindNext strCriteria
        If rs.NoMatch Then
            MsgBox "No more matches"
            bRepeat = False
        Else
            Me.Bookmark = rs.Bookmark
        End If
    End If
    
    rs.Close
    Set rs = Nothing

End Sub

Select allOpen in new window

"This problem is intermittent. "
Clearly, it's going to be difficult for us to zero in on this.  I suggest that you trap those specific errors, such that you can break into code and examine your various parameters (criteria, etc) ... and determine the specific times when this occurs.

Error 3252  is really odd ...

Hi fyed,
My requirement is a little different than yours: I'm not searching for a record that meets the criteria, I'm trying to determine whether the current record meets the criteria. But thanks anyway.

Hi mx,
<Clearly, it's going to be difficult for us to zero in on this> without more info.
Understood; I only posted this question now in the hopes that someone else has experienced this problem and knew of a solution.

I will investigate further and post more info as I get it.

Then why are you using the FindFirst and FindNext method, if you are not trying to find a record that matches a specific critieria?

Why don't you explain to us what you are really trying to do, and see if we can help you accomplish that, rather than trying to find the "bug" in your code without fully understanding what you are trying to accomplish.

from your earlier post # 37003363:
"It gets pretty involved (and I don't expect to you analyze my entire database), but what this code is supposed to do is determine if the current record of a form meets given Criteria (which is a string in the form of a Where clause). Is there another another way to do this (that will work whether or not the recordset has a unique index)?"

1.  What event are you calling this code in?  If the current record, then generally use the Form_Current event
2.  What field of your current record are you interested in?  I don't see any reference to a field.  It may be in your "Criteria" string, but there is no indication of that.  Can you print out an example of that criteria looks like?

Hi fyed,
<Then why are you using the FindFirst and FindNext method>
As I said, I'm trying to determine whether the current record meets the criteria.

<Why don't you explain to us what you are really trying to do, and see if we can help you accomplish that, rather than trying to find the "bug" in your code without fully understanding what you are trying to accomplish.>
I thought I did that: see the code snippet with the original question, and this: <what this code is supposed to do is determine if the current record of a form meets given Criteria (which is a string in the form of a Where clause). Is there another another way to do this (that will work whether or not the recordset has a unique index)? >

<What event are you calling this code in?>
<What field of your current record are you interested in?>
This code is from a generic procedure I have created that is called by several events of several forms (and will be called by other events of other forms that I create in the future) whenever I have a need to determine if the current meets a particular criteria (which also varies from calling event to calling event and form to form). For example, I use this procedure to determine if field A should be locked depending on the values in fields B and C.

I finally understand what the code you provided is trying to do.

Your intent is to step through the current recordset using the Criteria and either FindFirst or FindNext to find the first record (from the current recordpointer) which meets your criteria.  If that records bookmark doesn't match the bookmark of the currently displayed record, then return False, if they match, return True.  In this circumstance, I cannot think of any reason why you would need to use FindFirst.  Try replacing:

      rs.Move -1
      If rs.BOF Then
         rs.FindFirst Criteria
      Else
         rs.FindNext Criteria
      End If
           
      If Not rs.NoMatch Then
         If StrComp(rs.Bookmark, Me.Bookmark, vbBinaryCompare) = 0 Then CMC = True
      End If

with:

      if rs.AbsolutePosition > 0 Then rs.move -1
      rs.FindNext Criteria
      If not rs.NoMatch Then CMC = StrComp(rs.Bookmark, Me.Bookmark, vbBinaryCompare) = 0

This checks to see whether the rs is on the first record, before backing up one record.
It then uses FindNext to find the first subsequent record that matches your search.  If that record doesn't match, then it sets CMC to False, if it does match, then it sets CMC to True.

Hi Fyed,
I'm not sure what the advantage is to your code, or how it solves my isue, but I do see a problem: I don't think it will work if the current record matches the criteria and has absoluet position zero. In that case wouldn't your code provide the result FALSE, which would be incorrect?

Hi fyed,
I like your code (it's more elegant than mine). Thanks.

However, I don't think it's going to solve the problem: Since the error occurs on the FindNext line, it means the current record is not the first record (AbolutePosition<>0), and in this case, both my and your code will execute:
     rs.move -1
     rs.FindNext Criteria

 

Try it and see.  

As I said I think your problem is that your code performs the move before checking where you are in the recordset.  My code checks to see whether you are already on the first record, before moving.

Hi fyed,
<As I said I think your problem is that your code performs the move before checking where you are in the recordset.  My code checks to see whether you are already on the first record, before moving.>
The problem only occurs when the current record is NOT the first record, and in that case your code and my code execute the same lines: ie Move -1 and then Find Next.

Does the form you are using have a sort order?  If so, you could modify the search criteria to use that sort order to your benefit, something like:

    strCriteria = "cstr([lngNumber]) Like ""*" & Me.txt_Find & "*"" AND " _
                     & "[ID] >= " & me.txt_ID

Then you won't need to use the FindNext at all, you could use the findfirst and not have to worry about moving to the previous record.

Hi Fyed,
There is no sort order.

Hi Fyed,
Since using your idea, I've had no errors. Because I've made other chanegs, and the problem is intermittent, I can't say for sure that your solution solved the problem, but  I will give it the benefit of the doubt. Full points to you, sir. Thanks for your help!

glad I could help.