In MS Access, a subform is a form that is embedded in another form, called the main form. A subform can display data from a related table or query, and it can also be used to enter or edit data in the related table. A subform can have its own filter, which is a set of criteria that determines which records are displayed in the subform. A filter can be applied to a subform by using the filter button on the subform’s toolbar, by using the filter by form feature, or by using VBA code.
Sometimes, you may want to permanently remove the filter from a subform, so that it displays all the records from the related table or query. This can be useful if you want to see the complete data set, or if you want to apply a different filter to the subform. In this article, we will show you how to permanently remove filter from subform in MS Access, using three different methods: clearing the filter property, setting the filter on property to false, and changing the record source property.
Clearing the Filter Property
One way to permanently remove filter from subform in MS Access is to clear the filter property of the subform. The filter property is a string that contains the filter criteria for the subform. To clear the filter property, you can use the following VBA code:
Me.SubformName.Form.Filter = ""
where SubformName is the name of the subform control on the main form. This code sets the filter property of the subform to an empty string, which means no filter is applied. You can run this code from a command button on the main form, or from any other event that triggers the removal of the filter.
Setting the Filter On Property to False
Another way to permanently remove filter from subform in MS Access is to set the filter on property of the subform to false. The filter on property is a boolean value that determines whether the filter property is applied to the subform. To set the filter on property to false, you can use the following VBA code:
Me.SubformName.Form.FilterOn = False
where SubformName is the name of the subform control on the main form. This code sets the filter on property of the subform to false, which means the filter property is ignored. You can run this code from a command button on the main form, or from any other event that triggers the removal of the filter.
Changing the Record Source Property
A third way to permanently remove filter from subform in MS Access is to change the record source property of the subform. The record source property is a string that specifies the source of data for the subform, which can be a table name, a query name, or a SQL statement. To change the record source property, you can use the following VBA code:
Me.SubformName.Form.RecordSource = "SELECT * FROM TableName"
where SubformName is the name of the subform control on the main form, and TableName is the name of the table or query that contains the data for the subform. This code sets the record source property of the subform to a SQL statement that selects all the records from the table or query, without any filter. You can run this code from a command button on the main form, or from any other event that triggers the removal of the filter.
