[AccessD] finding # of filtered records

Thanks for the ideas. Maybe its my thought process thats is off. I am
allowing the user to right click on the forms text boxes and use the right
click menu (filter by selection, filter for, etc). Thus they would hit
enter on the keyboard rather than a command button. I was hoping to pop a
msgbox and repopulate the form, rather than just having it go blank. I do
have a cmd button to repopulate but was thinking this was not the most
elegant way. I guess I thought when a rs was filtered it would have a new
count, or maybe even be a new rs. The code I was hoping would work was
If Me.RecordsetClone.RecordCount <1 Then
    Me.FilterOn = False
End If
in the forms apply filter event, but this seems to return the original
record count. I guess for now I will just have them repopulate manually.
Preciate the help.

Lyle Hannum



                                                                                                      
                    "Neal Kling"                                                                      
                    <nkling at co.montgomery.ny       To:     <accessd at databaseadvisors.com>             
                    .us>                           cc:                                                
                    Sent by:                       Subject:     RE: [AccessD] finding # of filtered   
                    accessd-admin at databasead        records                                           
                    visors.com                                                                        
                                                                                                      
                                                                                                      
                    02/20/03 01:52 PM                                                                 
                    Please respond to                                                                 
                    accessd                                                                           
                                                                                                      
                                                                                                      




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
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com