FindNext Error

I am getting errors when I use the FindNext method of a recordsetclone object (see code snippet).
What's puzzling is:
 - If I step through the code after FindNext causes an error, I find that the FindFirst statement works fine.
 - If I step through the code after FindNext causes an error, and change the Criteria, I can get it to work without error.
 - This problem is intermittent.
 - I've tried reconstructing the database by importing all database object into a new mdb file, and the problem is still there.

I know this isn't much to go on, but can anyone suggest what the problem might be?
Dim rs As DAO.Recordset
   Dim CMC As Variant   
   
   'Initialize
   Set rs = Me.RecordsetClone
   
   'MAIN

   If rs.EOF Then 
      CMC = Null
      
   Else
      CMC = False
      rs.Bookmark = Me.Bookmark 'Synchronize
      
      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
      
   End If

Open in new window

LVL 1
MilewskpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael VasilevskySolutions ArchitectCommented:
What's the error?
0
MilewskpAuthor Commented:
I get at least two different errors from time to time (Intermittent):
Error 3219 Object Invalid or no longer set
Error ???? Invalid Operation
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
What happens if you change

rs.Bookmark = Me.Bookmark 'Synchronize

to


Me.Bookmark = rs.Bookmark 'Synchronize

?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Are you positive the error is occurring on the FindNext line ?

mx
0
MilewskpAuthor Commented:
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.




0
MilewskpAuthor Commented:
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.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0
MilewskpAuthor Commented:
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)?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
When/How does this code get executed ?
0
Dale FyeCommented:
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

Open in new window

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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 ...
0
MilewskpAuthor Commented:
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.

0
MilewskpAuthor Commented:
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.
0
Dale FyeCommented:
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?
0
MilewskpAuthor Commented:
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.
0
Dale FyeCommented:
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.

0
MilewskpAuthor Commented:
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?
0
Dale FyeCommented:
Yes.

I think your problem has to do with your rs.Move command moving the pointer before testing if you were on the first record.  Try:

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

Dale
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MilewskpAuthor Commented:
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

 
0
Dale FyeCommented:
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.
0
MilewskpAuthor Commented:
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.
0
Dale FyeCommented:
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.


0
MilewskpAuthor Commented:
Hi Fyed,
There is no sort order.
0
MilewskpAuthor Commented:
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!
0
Dale FyeCommented:
glad I could help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.