?
Solved

FindNext Error

Posted on 2011-10-20
26
Medium Priority
?
529 Views
Last Modified: 2013-11-29
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

0
Comment
Question by:Milewskp
  • 12
  • 7
  • 6
  • +1
26 Comments
 
LVL 10

Expert Comment

by:Michael Vasilevsky
ID: 37002222
What's the error?
0
 
LVL 1

Author Comment

by:Milewskp
ID: 37002674
I get at least two different errors from time to time (Intermittent):
Error 3219 Object Invalid or no longer set
Error ???? Invalid Operation
0
 
LVL 75
ID: 37003052
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 75
ID: 37003087
What happens if you change

rs.Bookmark = Me.Bookmark 'Synchronize

to


Me.Bookmark = rs.Bookmark 'Synchronize

?
0
 
LVL 75
ID: 37003116
Are you positive the error is occurring on the FindNext line ?

mx
0
 
LVL 1

Author Comment

by:Milewskp
ID: 37003236
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
 
LVL 1

Author Comment

by:Milewskp
ID: 37003246
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
 
LVL 75
ID: 37003265
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
 
LVL 1

Author Comment

by:Milewskp
ID: 37003363
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
 
LVL 75
ID: 37003381
When/How does this code get executed ?
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37003545
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
 
LVL 75
ID: 37003970
"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
 
LVL 1

Author Comment

by:Milewskp
ID: 37004086
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
 
LVL 1

Author Comment

by:Milewskp
ID: 37004095
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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37007777
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
 
LVL 1

Author Comment

by:Milewskp
ID: 37017688
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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37017905
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
 
LVL 1

Author Comment

by:Milewskp
ID: 37018063
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
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 37018109
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
 
LVL 1

Author Comment

by:Milewskp
ID: 37056658
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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37056715
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
 
LVL 1

Author Comment

by:Milewskp
ID: 37078899
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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37079737
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
 
LVL 1

Author Comment

by:Milewskp
ID: 37109234
Hi Fyed,
There is no sort order.
0
 
LVL 1

Author Comment

by:Milewskp
ID: 37109269
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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37109305
glad I could help.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question