Full Version: FindNext doesn't work
UtterAccess Forums > Microsoft® Access > Access Forms
wind54surfer
Hi all,
I have the same sub as the one below to FindFirst and it works great.
I am wondering what I am doing wrong that can't get it to work.
Any help is greatly appeciated,
Emilio
Private Sub btnFindNext_Click()
On Error GoTo Err_btnFindNext_Click

' Find the Next record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindNext "[CustomerName] = '" & Me![FindName] & "'"
If rs.NoMatch Then

MsgBox "There isn't anymore, " & [FindName] & ", Jobs."
Else
Me.Bookmark = rs.Bookmark
End If
Exit_btnFindNext_Click:
Exit Sub
Err_btnFindNext_Click:
MsgBox Err.Description
Resume Exit_btnFindNext_Click

End Sub
goblinstands
Perhaps you shouldn't have this line in your code:
et rs = Me.Recordset.Clone
Since you have to do a FindFirst before FindNext, I think that resetting the rs Object and then using FindNext immediately is causing the error. Perhaps you should also make rs a module-level variable that is set to the recordset when the form opens, then is set to Nothing when it closes. That way it will stay in position regardless of what event fires.
wind54surfer
Thanks a lot for your reply.
Odon't have that much knowledge to do what you said.
Is anyway you can expand a little more maybe with an example.
Thanks a lot,
Emilio
goblinstands
Alright, I'm assuming that you have a btnFindFirst_Click procedure as well, with similar code and FindFirst instead of FindNext? When you do a find on a recordset you cannot do a FindNext unless you've done FindFirst first, because it will not know what to look for next. When you declare rs as an Object inside a sub procedure like that, it only has scope (the "value" exists) in that procedure. Once you leave it (after the button has been clicked), the rs value cannot be accessed in another procedure, thus the FindFirst you did previously cannot be accessed by the FindNext button. In essence, you probably have two separate rs objects that are out of sync with each other. But, if you place the rs variable outside all the procedures, it can be accessed by all.
So, at the beginning of the form module, before any sub procedure, you should put something like this:
CODE
Option Compare Database
Option Explicit

Private rs as Object

We'll initialize the object when the form loads, then destroy it when the form unloads. In the Form_Load procedure, put this:
CODE
Private Sub Form_Load()
   Set rs = Me.Recordset.Clone
End Sub

In btnFindFirst_Click (or whatever it's called), put this:
CODE
Private Sub btnFindFirst_Click()
On Error GoTo Err_btnFindFirst_Click
   'Reset the cursor to the beginning of the recordset
   '  each time you look for a new name
   rs.MoveFirst
   rs.FindFirst "[CustomerName] = '" & Me![FindName] & "'"
   If rs.NoMatch Then
      MsgBox "There isn't anymore, " & [FindName] & ", Jobs."
   Else
      Me.Bookmark = rs.Bookmark
   End If
Exit_btnFindFirst_Click:
   Exit Sub
Err_btnFindFirst_Click:
   MsgBox Err.Description
   Resume Exit_btnFindFirst_Click
End Sub

Then, change your FindNext to this:
CODE
Private Sub btnFindNext_Click()
On Error GoTo Err_btnFindNext_Click
   rs.FindNext "[CustomerName] = '" & Me![FindName] & "'"
   If rs.NoMatch Then
      MsgBox "There isn't anymore, " & [FindName] & ", Jobs."
   Else
      Me.Bookmark = rs.Bookmark
   End If
Exit_btnFindNext_Click:
   Exit Sub
Err_btnFindNext_Click:
   MsgBox Err.Description
   Resume Exit_btnFindNext_Click
End Sub

Lastly, you'll need to destroy the rs Object that you initialized in Form_Load. In the Form_Close event put this:
CODE
Private Sub Form_Unload()
   Set rs = Nothing
End Sub

See what that does. If you're still having problems, paste or attach the entire code behind the module...maybe there's something else going on. Hope this helps.
ChrisO
This might be another possibility.
sing Me.RecordsetClone directly (probably Me.Recordset.Clone in A2K3) removes the need to create and destroy a Recordset.
CODE
Private Sub cmdFirst_Click()

    GoToBookmark "First"
    
End Sub


Private Sub cmdNext_Click()

    GoToBookmark "Next"

End Sub


Private Sub GoToBookmark(ByVal strPosition As String)
    
    With Me.RecordsetClone
        If strPosition = "First" Then
            .MoveFirst
            .FindFirst "[SubValue] = " & 21
        Else
            .FindNext "[SubValue] = " & 21
        End If
        
        If .NoMatch Then
            MsgBox "No record found."
        Else
            Me.Bookmark = .Bookmark
        End If
    End With

End Sub
Obviously the Field names will need changing to suit.
Hope that helps.
Regards,
Chris.
wind54surfer
Thanks a million
I have been fighting with this for a long time.
You ARE the Man!
milio
wind54surfer
Thanks for helping Chris,
Otried the other solution first because I have FindFirst in a AfterUpdate of a Combo List and it worked.
Thanks again,
Emilio
goblinstands
Glad it worked. If you ever do a redesign, make new forms, etc, you should probably try Chris' solution instead...the less objects that you have to create and maintain, the better. Not to mention less code...
wind54surfer
Hi Chris,
decided to give your code a try since it is so small.
But I tried to add a twist FindPrevious with my limited knowledge, I experimented in many ways and can't make it go to the Previous record, only to Next.
Owonder if you can help me one more time.
Greatly appreciated,
Emilio
THere is my "code"?
Private Sub FindName_AfterUpdate()

GoToBookmark "First"

End Sub
--------------------------
Private Sub btnFindNext_Click()

GoToBookmark "Next"

End Sub
-------------------------
Private Sub btnFindPrevious_Click()

GoToBookmark "Previous"

End Sub
--------------------------
Private Sub GoToBookmark(ByVal strPosition As String)

With Me.RecordsetClone
If strPosition = "First" Then
.MoveFirst
.FindFirst "[CustomerName] = '" & Me![FindName] & "'"

ElseIf strPosition <> "First" Then
.FindNext "[CustomerName] = '" & Me![FindName] & "'"

Else
If strPosition = "Next" Then
.FindPrevious "[CustomerName] = '" & Me![FindName] & "'"

End If
End If

If .NoMatch Then
MsgBox "No record found."
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub
ChrisO
There is a logical error in your If structure.

There may also be a requirement to FindLast so I’ll Switch to the Select Case structure.
(If you have used ‘C’ that is almost a pun…sorry about that. smirk.gif )

CODE
Private Sub cmdFirst_Click()
    GoToBookmark "First"
    
End Sub


Private Sub cmdNext_Click()

    GoToBookmark "Next"

End Sub


Private Sub cmdPervious_Click()

    GoToBookmark "Previous"

End Sub


Private Sub GoToBookmark(ByVal strPosition As String)
    Dim strCriteria As String
    
    strCriteria = "[SubValue] = " & 21
    
    With Me.RecordsetClone
        Select Case strPosition
        
            Case "First"
                .MoveFirst
                .FindFirst strCriteria
                
            Case "Next"
                .FindNext strCriteria
        
            Case "Previous"
                .FindPrevious strCriteria
                
        End Select
        
        If .NoMatch Then
            MsgBox "No record found."
        Else
            Me.Bookmark = .Bookmark
        End If
    End With

End Sub
Please also notice that GoToBookmark "First" can be used within the AfterUpdate() of your ComboBox.

Quid pro quo…please modify the above code to include a Find Last search.

Hope that helps, experiment and have some fun with it.

Regards,
Chris.


Edited by: ChrisO on Sat Oct 23 22:25:51 EDT 2004.
Edited by: ChrisO on Sat Oct 23 22:27:18 EDT 2004.
wind54surfer
Sweet!
The code gets even smaller
Thanks again for your help and patience, and you bet I will experiment with it.
Regards,
Emilio
ChrisO
I like your attitude. grin.gif

Once you have Find Last working I’ll give a shot at showing you how to remove four subroutines if you haven’t already seen it.
Hint…you might note a redundancy in the callers.

Kind regards,
Chris.

Edited by: ChrisO on Sun Oct 24 0:07:38 EDT 2004.
wind54surfer
Hi Chris,
don't need a FindLast but if I did it would be simple (with your code)
Case "Last"
.FindLast strCriteria
But what intrigues me is, how do you do away with the routines?
They all work on the Click except First is AfterUpdate.
Is not like I can have 1 button doing everything.
But of course I don't know much , but like to learn.
Thanks again,
Emilio
ChrisO
G’day Emilio.
That is correct.
Now for trashing some code…
If you change: -
Private Sub GoToBookmark(ByVal strPosition As String)
to
Private Function GoToBookmark(ByVal strPosition As String)
And in the OnClick event, which is now set at [Event Procedure], you can insert: -
=GoToBookmark("First") for the “First“ button and similarly for the other buttons.
If you do that then the event procedures for the buttons are by-passed and the code goes directly to the common GoToBookmark function. It also passes an argument to tell the common procedure what to do.
Since the individual event procedures for the buttons are by-passed they have become redundant and can be removed.
Give that a shot and see how you go.
What I’m aiming at here is one common event handler to change bookmarks and another to enable/disable the buttons in the Combo Box AfterUpdate event.
Regards,
Chris.
wind54surfer
Is more like G'vening where I am,
OChris
reat, and as a bonus I can still use the procedure, in the case of AfterUpdate I had to add a setfocus property and thought that maybe I lost it with this new way of making it work, but it works like a charm.
Thanks again,
Emilio
ChrisO
G'evening Emilio.
don’t know about the set focus thing but this is all I needed to get it to work: -
CODE
Option Explicit
Option Compare Text


Private Function GoToBookmark(ByVal strPosition As String)
    Dim strCriteria As String
    
    strCriteria = "[SubValue] = " & Me.cboSubValues
    
    With Me.RecordsetClone
        Select Case strPosition
        
            Case "First"
                .MoveFirst
                .FindFirst strCriteria
                  
            Case "Previous"
                .FindPrevious strCriteria
              
            Case "Next"
                .FindNext strCriteria
  
            Case "Last"
                .FindLast strCriteria
                  
        End Select
          
        If .NoMatch Then
            MsgBox "No record found."
        Else
            Me.Bookmark = .Bookmark
        End If
    End With

End Function


Private Sub cboSubValues_AfterUpdate()
    Dim blnEnabled As Boolean

    blnEnabled = Not Nz(Me.cboSubValues, "") = ""

    Me.cmdFirst.Enabled = blnEnabled
    Me.cmdPervious.Enabled = blnEnabled
    Me.cmdNext.Enabled = blnEnabled
    Me.cmdLast.Enabled = blnEnabled
    
    If (blnEnabled) Then
        GoToBookmark "First"
    End If
    
End Sub
Have fun and there is more info available about those event handlers if you require it.
Regards,
Chris.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.