Test for ADO Find Method

[dynamictiger]

As part of a nested function I am passing criteria to an ADO recordset which is using find to find matching records.

If there is no match ADO kicks me out without an error message.

How do I test my criteria to see if there is a match from within the find method?

 

[FancyPrairie]

Try this:

if (rst.eof) and (rst.bof) then msgbox "no match"

 

[vbajock]

Check the help files for the NoMatch property.

rst.movefirst
rst.find somevalue

if rst.NoMatch = true then
Didn't find record
else
Did find it
end if

Don't forget the .movefirst if it is in a loop. If you don't set it to the first record, the next time you make a pass the record pointer will be at eof

 

[Nikita6003]

One word of caution:

NoMatch

will only work for DAO recordsets using the Jet workspace!

Another word of caution: the ADO

Find

method can't deal with the IS operator

And a final word of caution: It can't deal with multiple conditions using

And

I had to change my code to use the

Filter

method instead. Caused me LOADS of frustrations & many dents in my PC ... ;-)

HTH

Cheers
Nikki

 

[dynamictiger]

Thanks I had my rst.BOF and rst.EOF in the wrong spot

 

[vbajock]

Yea, my bad. You have to test for EOF using FIND in ADO. I actually knew that, but I just spaced it out. One question: why you would test for BOF? The pointer goes to EOF if the record is not found. Wouldn't BOF be false and EOF true if the table has records and the FIND failed?

I didn't know about the limitations on FIND you mentioned, thanks for letting us know. I hardly ever use it. SEEK seems to be the one that I always end up using, because it is so much faster, but then again, you have to set up indexes.