FindNext not working... ??

[max]

Hi all,

I've created two command buttons on my form, with the standard actions
FindFirst and FindNext. The first buttons finds the firs matching record,
but the FindNext button doesn't produce any change.... no error message ....

here's the code:

************************ code starts here ************************

Private Sub cmdFindMedia_Click()

On Error GoTo Err_cmdFindMedia_Click



'Dim rs As Object



strSearch = InputBox("Type the word or part of the word you want to
search.", "Search in the Media Title")



Set rs = Me.Recordset.Clone

rs.FindFirst "medTitle = '" & strSearch & "'"

Me.Bookmark = rs.Bookmark



Exit_cmdFindMedia_Click:

Exit Sub



Err_cmdFindMedia_Click:

MsgBox Err.Description

Resume Exit_cmdFindMedia_Click



End Sub



----------------------------------------------------------------------------
------



Private Sub cmdFindNextMedia_Click()

On Error GoTo Err_cmdFindNextMedia_Click



Dim rs As Object

Set rs = Me.Recordset.Clone

rs.FindNext "medTitle = '" & strSearch & "'"

Me.Bookmark = rs.Bookmark



Exit_cmdFindNextMedia_Click:

Exit Sub



Err_cmdFindNextMedia_Click:

MsgBox Err.Description

Resume Exit_cmdFindNextMedia_Click



End Sub


************************ code ends here ************************
any idea?

thanks a lot

Max

 

[Dirk Goldgar]

Hi all,

I've created two command buttons on my form, with the standard actions
FindFirst and FindNext. The first buttons finds the firs matching
record, but the FindNext button doesn't produce any change.... no
error message ....

here's the code:

************************ code starts here ************************

Private Sub cmdFindMedia_Click()

On Error GoTo Err_cmdFindMedia_Click



'Dim rs As Object



strSearch = InputBox("Type the word or part of the word you want
to search.", "Search in the Media Title")



Set rs = Me.Recordset.Clone

rs.FindFirst "medTitle = '" & strSearch & "'"

Me.Bookmark = rs.Bookmark



Exit_cmdFindMedia_Click:

Exit Sub



Err_cmdFindMedia_Click:

MsgBox Err.Description

Resume Exit_cmdFindMedia_Click



End Sub



---------------------------------------------------------------------- ------
------



Private Sub cmdFindNextMedia_Click()

On Error GoTo Err_cmdFindNextMedia_Click



Dim rs As Object

Set rs = Me.Recordset.Clone

rs.FindNext "medTitle = '" & strSearch & "'"

Me.Bookmark = rs.Bookmark



Exit_cmdFindNextMedia_Click:

Exit Sub



Err_cmdFindNextMedia_Click:

MsgBox Err.Description

Resume Exit_cmdFindNextMedia_Click



End Sub


************************ code ends here ************************
any idea?

thanks a lot

Max

Seems to me you're first going to have to synchronize the recordset
clone to the current record. Maybe this would do it:

'----- start of revised code -----
Private Sub cmdFindNextMedia_Click()

On Error GoTo Err_cmdFindNextMedia_Click

Set rs = Me.Recordset.Clone

rs.Bookmark = Me.Bookmark

rs.FindNext "medTitle = '" & strSearch & "'"

If .NoMatch Then
MsgBox "No more records found for '" & strSearch & "'"
Else
Me.Bookmark = rs.Bookmark
End If

Exit_cmdFindNextMedia_Click:
Set rs = Nothing
Exit Sub

Err_cmdFindNextMedia_Click:
MsgBox Err.Description
Resume Exit_cmdFindNextMedia_Click

End Sub
'----- end of revised code -----

 

[max]

Hey Dirk, thanks a lot for your code, it works perfectly!
I have also seen that you set the rs object = nothing in the end, should I
do the same in the findfirst procedure?
In this case I suppose I have to declare it again with Dim in the second
procedure, am I correct?
in case I am, this is what I have written:

'----- start of code -----



Private Sub cmdFindMedia_Click()

On Error GoTo Err_cmdFindMedia_Click



Dim rs As Object



strSearch = InputBox("Type the word or part of the word you want to
search.", "Search in the Media Title")



Set rs = Me.Recordset.Clone

rs.FindFirst "medTitle = '" & strSearch & "'"



If rs.NoMatch Then

MsgBox "No records found for '" & strSearch & "'"

Else

Me.Bookmark = rs.Bookmark

End If



Exit_cmdFindMedia_Click:

Set rs = Nothing

Exit Sub



Err_cmdFindMedia_Click:

MsgBox Err.Description

Resume Exit_cmdFindMedia_Click



End Sub



----------------------------------------------------------------



Private Sub cmdFindNextMedia_Click()

On Error GoTo Err_cmdFindNextMedia_Click



Dim rs As Object



Set rs = Me.Recordset.Clone

rs.Bookmark = Me.Bookmark



rs.FindNext "medTitle = '" & strSearch & "'"



If rs.NoMatch Then

MsgBox "No more records found for '" & strSearch & "'"

Else

Me.Bookmark = rs.Bookmark

End If



Exit_cmdFindNextMedia_Click:

Set rs = Nothing

Exit Sub



Err_cmdFindNextMedia_Click:

MsgBox Err.Description

Resume Exit_cmdFindNextMedia_Click



End Sub



'----- end of code -----



And last question: this procedure would be really perfect for me if could
look for words that are part of the title, not just the whole title. I mean,
at the moment it looks for TEST, when I would like it to look for *TEST*, I
know it is possible, I've tried in several ways, but I always get a syntax
error... do you know where's the trick?



many thanks again,



Max

 

[Dirk Goldgar]

Hey Dirk, thanks a lot for your code, it works perfectly!
I have also seen that you set the rs object = nothing in the end,
should I do the same in the findfirst procedure?
In this case I suppose I have to declare it again with Dim in the
second procedure, am I correct?
in case I am, this is what I have written:

'----- start of code -----



Private Sub cmdFindMedia_Click()

On Error GoTo Err_cmdFindMedia_Click



Dim rs As Object



strSearch = InputBox("Type the word or part of the word you want
to search.", "Search in the Media Title")



Set rs = Me.Recordset.Clone

rs.FindFirst "medTitle = '" & strSearch & "'"



If rs.NoMatch Then

MsgBox "No records found for '" & strSearch & "'"

Else

Me.Bookmark = rs.Bookmark

End If



Exit_cmdFindMedia_Click:

Set rs = Nothing

Exit Sub



Err_cmdFindMedia_Click:

MsgBox Err.Description

Resume Exit_cmdFindMedia_Click



End Sub



----------------------------------------------------------------



Private Sub cmdFindNextMedia_Click()

On Error GoTo Err_cmdFindNextMedia_Click



Dim rs As Object



Set rs = Me.Recordset.Clone

rs.Bookmark = Me.Bookmark



rs.FindNext "medTitle = '" & strSearch & "'"



If rs.NoMatch Then

MsgBox "No more records found for '" & strSearch & "'"

Else

Me.Bookmark = rs.Bookmark

End If



Exit_cmdFindNextMedia_Click:

Set rs = Nothing

Exit Sub



Err_cmdFindNextMedia_Click:

MsgBox Err.Description

Resume Exit_cmdFindNextMedia_Click



End Sub



'----- end of code -----



And last question: this procedure would be really perfect for me if
could look for words that are part of the title, not just the whole
title. I mean, at the moment it looks for TEST, when I would like it
to look for *TEST*, I know it is possible, I've tried in several
ways, but I always get a syntax error... do you know where's the
trick?

Your revised code looks right to me. You should definitely declare rs
in both procedures; if the compiler doesn't force you to, it must be
because you don't have your VB options set to require explicit variable
declaration. I strongly recommend that you change that option so that
explicit decalarations of all variables are required -- not doing so can
lead to a lot of hard-to-track-down errors.

As for looking for a string that is part of the title, you could change
your FindFirst/FindNext criteria to this:

rs.FindFirst "medTitle Like '*" & strSearch & "*'"

That should work so long as you are working in an MDB file, not an ADP
(which uses different wild-card characters).

 

[max]

Yes, the "Require Variable Declaration" was not ticked in the Options diaolg
box.
The procedure now is exactly what I need. Thanks a lot for the code and the
precious advises!

Best Regards,
Massimo