Unable to filter records using a command button

[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.

 

[Steve Schapel]

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?

 

[Guest]

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.

 

[Tommy Keenze]

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.

 

[Peter R. Fletcher]

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.

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[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.

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Guest]

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.