Apply filter problem

[barit]

I am having a problem getting a subform's filter to work properly.

I have a from called "Phone" and a subform called "Phonesub". On the subform
I have an option group called LastNameFilters that contains alpha buttons.
As a subform everything works great. As expected, when an alpha button is
clicked, only those records related to that letter are shown. The problem is
when the subform is inserted on the main form, the option group (filter no
longer works). All records are displayed, however, when an alpha button is
pressed the records for the individual letters are not displayed. Only those
records related to A.

The code behind this option group is as follows

If (LastNameFilters = 1) Then
' Filter for Last names names that start with A, À, Á, Â, Ã, or Ä.
DoCmd.ApplyFilter "", "[LastName] Like ""[AÀÁÂÃÄ]*"""
End If
Etc to 27 where
If (LastNameFilters = 27) Then
DoCmd.ShowAllRecords
End If

If (RecordsetClone.RecordCount > 0) Then
DoCmd.GoToControl "LastName"
DoCmd.GoToControl "LastName"
Exit Sub

End If
If (RecordsetClone.RecordCount = 0) Then
MsgBox "There are no records for that letter.", vbInformation, "No Records
Returned"
DoCmd.ShowAllRecords
LastNameFilters = 27
End If

What am I doing wrong? Why does it work as a standalone form but as a
subform not perform as expected. Suggestions would be greatly appreciated.

Thanks

 

[Moss100]

I am having a similar problem. What appears to be a easy fix is a nightmare. I have found this on MS site
==

This article was previously published under Q112796
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.

You cannot use the ApplyFilter action to apply a filter to a subform. If you try to do so, the filter is applied to the main form instead. In Microsoft Access version 1.x, you can achieve some of this functionality by basing the subform on a parameter query. In the other versions of Microsoft Access, you can get the same results by changing the subform's RecordSource property.
MORE INFORMATION
The ApplyFilter action dynamically restricts or sorts the records in a table or the records from a form or report's underlying table or query. Although you cannot use this action with a subform, you can change the subform's RecordSource property to achieve the same results.

The following example demonstrates how to change the sort order of a subform by changing the subform's RecordSource property. It uses the Categories form and Product List subform in the sample database Northwind.mdb (or the Categories form and Categories Subform subform in the sample database NWIND.MDB in 2.0).

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file or perform these steps on a copy of the Northwind database.

Open the sample database Northwind.mdb (NWIND.MDB in version 2.0) and create a new query based on the Products table. Include all the fields from the table in the query.
Set the UnitPrice field's Sort row to Ascending.

NOTE: In Microsoft Access 2.0 the UnitPrice field is called Unit Price.
Save the query as UnitPriceSort.
Open the Categories form in Design view, and then add a command button to the form.
Set the command button's OnClick property to the following event procedure.

In Microsoft Access 7.0 and 97: If Me![Product List].Form.RecordSource = "Products" Then
Me![Product List].Form.RecordSource = "UnitPriceSort"
Else
Me![Product List].Form.RecordSource = "Products"
End If

In Microsoft Access 2.0:If Me![Categories Subform].Form.RecordSource = "Products" Then
Me![Categories Subform].Form.RecordSource = "UnitPriceSort"
Else
Me![Categories Subform].Form.RecordSource = "Products"
End If

View the Categories form in Form view.
Click the command button you added in step 4. Note that as you continue to click the command button, the form's sort order is toggled between the Unit Price and the Product ID fields.
REFERENCES
For more information about the RecordSource property, search for "RecordSource Property" using the Microsoft Access 97 Help Index.