Unable to filter records using a command button

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an MS Access 2003 database with 1,126 records of members in an organization. The records contain a field named strStatus in which some members are identified as “Active†and some as “Deceasedâ€, etc. There are 276 “Active†records.

I created a command button on the form that executes a macro. The macro command is “ApplyFilterâ€, and the Filter Name control contains the name of the underlying query: qselActiveMembers. The query produces correct results when executed directly, and the command button executes the macro. However, the database still shows all 1,126 records.

Similarly, I created a button to limit records to those with a checked option box. The underlying query for this macro works perfectly also, but not when executed thru the command button on the form.

I would be grateful for any suggestions to solve this rather perplexing problem.
 
Tommy,

Do you have the your 1126 records displayed in a subform, or is it a
continuous view form which is not a subform on another form?
 
The command button is on the main form, but there are subforms on it as well.

Something I have not identified has changed. I have had these filter buttons working until recently. The subforms have never been a problem in the past. Perhaps there is something about the 2003 version that is different from the 2000 version.
 
The records are on a main form, however there are
subforms for addresses and contact information. These
have not been a problem in the past. I have been fine-
tuning this database program since v 1.0. Don't know what
has chenged recently that caused this problem to appear.
 
I had to look up what ApplyFilter does when you hand it a Query (I
have only used it with a WHERE expression), and found that it applies
_the_ _WHERE_ _clause_ from the Query to the form's current Recordset.
Is it possible that the name(s) of one or more fields/controls have
been changed either in the Query or in the Form's Recordset in such a
way that they are no longer "congruent". I don't immediately see why
that should cause ApplyFilter to not work rather than to abort with an
error, but macros don't always abort when (IMHO) they should.

I have an MS Access 2003 database with 1,126 records of members in an organization. The records contain a field named strStatus in which some members are identified as “Active” and some as “Deceased”, etc. There are 276 “Active” records.

I created a command button on the form that executes a macro. The macro command is “ApplyFilter”, and the Filter Name control contains the name of the underlying query: qselActiveMembers. The query produces correct results when executed directly, and the command button executes the macro. However, the database still shows all 1,126 records.

Similarly, I created a button to limit records to those with a checked option box. The underlying query for this macro works perfectly also, but not when executed thru the command button on the form.

I would be grateful for any suggestions to solve this rather perplexing problem.
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
The VBA code would be:

Me.Filter = "strStatus = Active"
Me.FilterOn = True ' Don't forget this one!

I am now attempting to use VBA Code to execute the ApplyFilter command, but I can't seem to get the syntax right.

Putting the following text into the form Filter control:

strStatus=”Active”

Produces the results I want. However, I have an Access 2000 developers handbook showing that the syntax for the VBA code would be:

DoCmd.ApplyFilter: ”strStatus = Active”, or perhaps:

DoCmd.ApplyFilter: ”strStatus = ‘Active’”

However these do not work. Nothing is passed to the Form Filter control.

In v 20003 Help Content, the syntax given would produce:

DoCmd.ApplyFilter , "strStatus = 'Active'"

However, this does not pass anything to the Form Filter Control either.
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Thank you very much, Peter. I had to modify the code to read:

Me.Filter = "strStatus = 'Active'"

But adding the Me.FilterOn = True apparently did the magic.
 
Back
Top