[AccessD] finding # of filtered records

I'm guessing that Lyle wants to cancel the apply filter event if there
are no records.

Lyle,

One might consider doing this by testing the recordcount in the
ApplyFilter event:

  If Me.RecordsetClone.RecordCount < 1 Then
    MsgBox "!"
  Else
    MsgBox "OK"
  End If

However, this does not work when you use the funnel button to apply the
filter.  Evidently the Recordset property is simply not available there.
I don't understand this, but I'm not going to take the time to figure it
out.

There is a workaround:

Create a button on your form and use it to apply the filter:

  DoCmd.RunCommand acCmdApplyFilterSort

Now the record count in the ApplyFilter event works, but... it shows you
the record count prior to the filter being applied.  So this can't be
used.

The final workaround:

Apply the filter and test it in the same button (don't use the
ApplyFilter event at all).  This way the filter is already applied and
the code works.

  DoCmd.RunCommand acCmdApplyFilterSort
  If Me.RecordsetClone.RecordCount < 1 Then
    DoCmd.RunCommand acCmdRemoveFilterSort
  Else
    'OK
  End If

By the way, rather than using DoCmd you could also just use the filter
property of the form:

  Me.FilterOn = True 'or False

Neal Kling



-----Original Message-----
From: Charlotte Foust [mailto:cfoust at infostatsystems.com]
Sent: Thursday, February 20, 2003 12:52 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] finding # of filtered records


At the risk of stating the obvious, if your controls disappear, then you
have zero records returned.

Charlotte Foust

-----Original Message-----
From: lyle.hannum at co.wake.nc.us [mailto:lyle.hannum at co.wake.nc.us] 
Sent: Thursday, February 20, 2003 9:30 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] finding # of filtered records


Hi all,

I would like to find the # of records returned when a form filter is
applied in Access 2000. My forms controls disappear when no records are
found :(  I plan to do my check in the forms Apply Filter event. Thanks.

Lyle Hannum

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com