How to tell whether FindRecord has found anything

[Guest]

I have a form (with records of CDs) with a subform (showing contents of each
CD). I have an text box on the main form to enter text to be found, and a Go
button. The code for the button uses DoCmd.FindRecord to highlight the found
text in the subform. This works fine - each click on Go takes me to the next
instance of the text. Is there a way to tell when FindRecord has not found
the text, so that I can use this to go to the next record on the main form?

 

[Alex Dybenko]

Hi,
instead of DoCmd.FindRecord you can make your own form (or textbox) to find
text,
the code will look like:

with me.recordsetclone
.FindFirst "MyField=" & me.txtFind
if .NoMatch then
MsgBox "Nothing found!"
else
me.bookmark=.bookmark
end if

end with

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

Hi Alex,

I had already tried this but I got compiler error saying 'field not
recognised'. Maybe I had not put the correct square brackets, quote marks,
exclamation marks or whatever. Maybe because me.recordsetclone is for the
main form and not the subform, which is a subset of the main data? What does
me.bookmark = .bookmark do? Does that make the next Find start from the
previous position?

Regards,
Dave W

 

[Alex Dybenko]

hi,
yes, forgot about subform

so should be me.MySubform.Form. instaed of me.

me.bookmark = .bookmark - this sets record selector on a form to row, where
record was found

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

Hi,

Many Thanks for giving me the clue to solving my problem. I have three
fields in my subform, each containing several words, and each operation to
find one word highlights each one in turn. However I had big difficulty in
doing the same thing as my original line which was:

DoCmd.FindRecord stEntry, acAnywhere, True, acDown, False, acAll, False
(stEntry string is equivalent to me.txtFind in your example).

To use Find, my equivalent line is:
..FindNext "MyField1 & MyField2 & MyField3 like '*" & stEntry & "*'"

For the case of match found, I had to repeat my DoCmd line after your
me.bookmark = .bookmark, to highlight the found word rather than just the
first field. Even then, this only highlights the first match in the row, but
I can live with that.

For the case of match not found, I set the focus to the main form,
GoToRecord (the next one by default), and set the focus back to the subform.
However I had to add the line .MoveFirst (presumably to set a new bookmark
and clear .NoMatch), otherwise no more matches were found. After this I added
the name of the subroutine itself, followed by Exit Sub, so that the search
continues through records on the main form until the next match is found in
the subform.

Problem solved, but I wish .NoMatch applied to DoCmd.FindRecord as well as
Recordset.FindNext

Actually I still have a small problem in that the find is case-insensitive,
even though Option Compare is Binary (I'm using Access 97).

Best regards,
Dave W