Testing to see if a filter found any matches 
Author Message
 Testing to see if a filter found any matches

How do you programmatically test to see whether a filter finds any matches?
 I'm using code such as

        Me.Filter  =  "criteriastring"

to search for matching records, but it's part of a loop, and I want to test
to see whether any matches are found, so I can exit the loop in that case.
Two questions:

1)  What code can I insert to perform the test? , and
2)  Is there any way, using VBA, to determine how many matches were found
so I can use the number elsewhere in my code?

Thanks.

Please remove "NOSPAM" from my address for any e-mail.



Sat, 06 Nov 1999 03:00:00 GMT  
 Testing to see if a filter found any matches

Paul, after setting the filter and after Me.FilterOn (I generally turn
Me.Filteron false before setting the .Filter string and back on after) you
can check the results by looking at the Recordsetclone property.

if Me!RecordSetclone.RecordCount > 0 Then
   ' You've got records.

Generally, with small recordsets the RecordCount property will be accurate
so you can change the syntax somewhat to get the count.

Dim lngCount as Long
lngCount = Me.Recordsetclone.Recordcount

Then check for > 0 and use the lngCount result elsewhere.

For large recordsets, Recordcount will not be accurate until Access has
found the last record. You can force this before getting the count by
moving to the last record. It's probably better in this case to change the
syntax a bit.

With Me!RecordsetClone
    lngCount = .RecordCount
    If lngCount > 0 Then
       .MoveLast
       lngCount = .RecordCount
    End If
End With

You mention calling this in a loop. It would likely work best as a function
which could be called from the loop.

Function GetRecordCount(frm as Form) as Long
     With  frm.RecordSetClone
         <same code as above but use "GetRecordCount" instead of lngCount>
     End With
End Function

and call it with,  lngCount = GetRecordCount(Me)

dorman blackman  



Quote:
> How do you programmatically test to see whether a filter finds any
matches?
>  I'm using code such as

>    Me.Filter  =  "criteriastring"

> to search for matching records, but it's part of a loop, and I want to
test
> to see whether any matches are found, so I can exit the loop in that
case.
> Two questions:

> 1)  What code can I insert to perform the test? , and
> 2)  Is there any way, using VBA, to determine how many matches were found
> so I can use the number elsewhere in my code?

> Thanks.

> Please remove "NOSPAM" from my address for any e-mail.



Sun, 07 Nov 1999 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. test the return value of aFolder.items.find(filter) method

2. Seek Nearest Match if exact match not found ?

3. Seek Nearest Match if exact match not found ?

4. TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST

5. matching a control name on a form with test in a field

6. Finding records that don't match

7. Partial Matches using Restrict or Find

8. Has the find method a partial match operator?

9. Using FIND method to get Exact match

10. using Find Method for Exact match

11. How to test if a filter returns no tasks

12. Select match of ThisDocument.Content.Find

 

 
Powered by phpBB® Forum Software