Remove filter/sort at Subform level

[Guest]

I have a form with a subform. I can set a filter on the main form with no
problem. I can also set a filter on the values of a subform.

However, when I try to remove the filter from the subform the filter at the
main form is removed instead. The subform filter remains. So far the only
work around I have for this problem is to close the main form and start over,
not a desirable situation.

Does anyone have a solution for this problem? Thanks.

 

[Guest]

How exactly are you attempting to remove the subform filter? In VBA? Post the
germane bit of code if so.

Instead of manipulating filters, I usually simply add a criteria entry in
the RecordSource of the form and/or subform. Then, when the criteria changes,
all I have to do it requery the form or subform.

For example, if the main form has a single CustomerID and the subform has a
list of all that customer's invoices, the query for the subform's source
might look like this:

SELECT Invoices.* from Invoices where Invoices.CustomerID =
[Forms]![CustomerInvoices]![CustomerID]

If the CustomerID on the CustomerInvoices form changes, all I have to do on
the CustomerInvoiceSub form is Me.Requery, and it now shows all the invoices
for the newly-selected CustomerID.

 

[Guest]

I'm simply trying to use the "Remove Filter/sort" option on the Access
short-cut menu. I believe what you are suggesting will work, but it seems
like a fair bit of coding. I would also need also need to handle sorting,
know if it is ascending/decending for the orderby.

How exactly are you attempting to remove the subform filter? In VBA? Post the
germane bit of code if so.

Instead of manipulating filters, I usually simply add a criteria entry in
the RecordSource of the form and/or subform. Then, when the criteria changes,
all I have to do it requery the form or subform.

For example, if the main form has a single CustomerID and the subform has a
list of all that customer's invoices, the query for the subform's source
might look like this:

SELECT Invoices.* from Invoices where Invoices.CustomerID =
[Forms]![CustomerInvoices]![CustomerID]

If the CustomerID on the CustomerInvoices form changes, all I have to do on
the CustomerInvoiceSub form is Me.Requery, and it now shows all the invoices
for the newly-selected CustomerID.

I have a form with a subform. I can set a filter on the main form with no
problem. I can also set a filter on the values of a subform.

However, when I try to remove the filter from the subform the filter at the
main form is removed instead. The subform filter remains. So far the only
work around I have for this problem is to close the main form and start over,
not a desirable situation.

Does anyone have a solution for this problem? Thanks.

 

[Guest]

I'm afraid I don't use the filter/sort manually much on forms (only when
looking at tables directly), but it sounds like when you go to the shortcut
menu, the action applies to the form, not the subform. (I always turn off all
the standard menus and control actions through buttons on my forms to avoid
having clever users delete records without due consideration, etc.)

Don't worry about a lot of coding for this part. Take a look at the query
builder that pops up when you click the ellipsis (three dots) by the
RecordSource of the form. This allows you to build a query that becomes the
record source of the form. Sorting is as easy as a double-click on the Sort
box for the field in question.

The key to filtering by this method is to have the user choose or enter the
filter criteria (for example, pick the customer from a combo box that lists
the customers) and then use the contents of that control as the criteria
portion of the query.

I'm simply trying to use the "Remove Filter/sort" option on the Access
short-cut menu. I believe what you are suggesting will work, but it seems
like a fair bit of coding. I would also need also need to handle sorting,
know if it is ascending/decending for the orderby.

How exactly are you attempting to remove the subform filter? In VBA? Post the
germane bit of code if so.

Instead of manipulating filters, I usually simply add a criteria entry in
the RecordSource of the form and/or subform. Then, when the criteria changes,
all I have to do it requery the form or subform.

For example, if the main form has a single CustomerID and the subform has a
list of all that customer's invoices, the query for the subform's source
might look like this:

SELECT Invoices.* from Invoices where Invoices.CustomerID =
[Forms]![CustomerInvoices]![CustomerID]

If the CustomerID on the CustomerInvoices form changes, all I have to do on
the CustomerInvoiceSub form is Me.Requery, and it now shows all the invoices
for the newly-selected CustomerID.

I have a form with a subform. I can set a filter on the main form with no
problem. I can also set a filter on the values of a subform.

However, when I try to remove the filter from the subform the filter at the
main form is removed instead. The subform filter remains. So far the only
work around I have for this problem is to close the main form and start over,
not a desirable situation.

Does anyone have a solution for this problem? Thanks.

 

[Guest]

Thanks for your reply.

Since the user is dynamically changing the sorting and filtering on the
form, doesn't the recordsource update also need to be dynamic? And to make
the update of recordsource dynamic don't you need to do a lot of VBA coding?

I also disable most toolbars menus. However, I allow a short cut menu. For
the user to do a filter or sort on a field they simply they to right click
and bring up the menu.

What I've found is the ApplyFilter event is only triggered at the form
level, and not the subform level, when you do a "Remove filter/sort"
operation. This happens even if you have an ApplyFilter event on the
subform, and you activate the Remove filter/sort from the same subform.

The best answer I've come up with so far is to use the ApplyFilter event of
the main form to determine if the request was made from the main or subform
level (using Screen.ActiveControl.Parent.Form), and then remove the
sort/filter with formname.FilterOn = False.

However, I don't under why all this should be necessary. Access can apply a
filter at the subform level, so it should handle removing a filter at the
subform level as well without all this additional work.

I'm afraid I don't use the filter/sort manually much on forms (only when
looking at tables directly), but it sounds like when you go to the shortcut
menu, the action applies to the form, not the subform. (I always turn off all
the standard menus and control actions through buttons on my forms to avoid
having clever users delete records without due consideration, etc.)

Don't worry about a lot of coding for this part. Take a look at the query
builder that pops up when you click the ellipsis (three dots) by the
RecordSource of the form. This allows you to build a query that becomes the
record source of the form. Sorting is as easy as a double-click on the Sort
box for the field in question.

The key to filtering by this method is to have the user choose or enter the
filter criteria (for example, pick the customer from a combo box that lists
the customers) and then use the contents of that control as the criteria
portion of the query.

I'm simply trying to use the "Remove Filter/sort" option on the Access
short-cut menu. I believe what you are suggesting will work, but it seems
like a fair bit of coding. I would also need also need to handle sorting,
know if it is ascending/decending for the orderby.

How exactly are you attempting to remove the subform filter? In VBA? Post the
germane bit of code if so.

Instead of manipulating filters, I usually simply add a criteria entry in
the RecordSource of the form and/or subform. Then, when the criteria changes,
all I have to do it requery the form or subform.

For example, if the main form has a single CustomerID and the subform has a
list of all that customer's invoices, the query for the subform's source
might look like this:

SELECT Invoices.* from Invoices where Invoices.CustomerID =
[Forms]![CustomerInvoices]![CustomerID]

If the CustomerID on the CustomerInvoices form changes, all I have to do on
the CustomerInvoiceSub form is Me.Requery, and it now shows all the invoices
for the newly-selected CustomerID.

:

I have a form with a subform. I can set a filter on the main form with no
problem. I can also set a filter on the values of a subform.

However, when I try to remove the filter from the subform the filter at the
main form is removed instead. The subform filter remains. So far the only
work around I have for this problem is to close the main form and start over,
not a desirable situation.

Does anyone have a solution for this problem? Thanks.

 

[Guest]

These little "features" are on reason I stay away giving users native Access
tools. Instead, I provide combo boxes where users can select filters for
various fields/controls and then include the content of these filter boxes in
the criteria section of the form's RecordSource query, and in the AfterUpdate
of the lookup box do a Me.Requery in VBA. It does require some fairly
extensive query design, but not much VBA code.

A simple example of a RecordSource query using a filtering combo box on the
form:

SELECT Customers.*
FROM Customers
WHERE (((Customers.CustomerID)=[Forms]![Customers]![CustomerIDFilter]) AND
(([Forms]![Customers]![CustomerIDFilter]) Is Not Null)) OR
((([Forms]![Customers]![CustomerIDFilter]) Is Null));

This filters CustomerID to CustomerIDFilter IF it is populated; otherwise it
does not filter on CustomerID. I have one form that has probably 15 - 20 such
filters (one for each of the important fields). It makes for pretty complex
queries, but not much VBA programming (just a requery of the form or combo
box).

This isolates the user one level from the developer's view of Access and
instead presents it in a more finished-application look.

Thanks for your reply.

Since the user is dynamically changing the sorting and filtering on the
form, doesn't the recordsource update also need to be dynamic? And to make
the update of recordsource dynamic don't you need to do a lot of VBA coding?

I also disable most toolbars menus. However, I allow a short cut menu. For
the user to do a filter or sort on a field they simply they to right click
and bring up the menu.

What I've found is the ApplyFilter event is only triggered at the form
level, and not the subform level, when you do a "Remove filter/sort"
operation. This happens even if you have an ApplyFilter event on the
subform, and you activate the Remove filter/sort from the same subform.

The best answer I've come up with so far is to use the ApplyFilter event of
the main form to determine if the request was made from the main or subform
level (using Screen.ActiveControl.Parent.Form), and then remove the
sort/filter with formname.FilterOn = False.

However, I don't under why all this should be necessary. Access can apply a
filter at the subform level, so it should handle removing a filter at the
subform level as well without all this additional work.

I'm afraid I don't use the filter/sort manually much on forms (only when
looking at tables directly), but it sounds like when you go to the shortcut
menu, the action applies to the form, not the subform. (I always turn off all
the standard menus and control actions through buttons on my forms to avoid
having clever users delete records without due consideration, etc.)

Don't worry about a lot of coding for this part. Take a look at the query
builder that pops up when you click the ellipsis (three dots) by the
RecordSource of the form. This allows you to build a query that becomes the
record source of the form. Sorting is as easy as a double-click on the Sort
box for the field in question.

The key to filtering by this method is to have the user choose or enter the
filter criteria (for example, pick the customer from a combo box that lists
the customers) and then use the contents of that control as the criteria
portion of the query.

I'm simply trying to use the "Remove Filter/sort" option on the Access
short-cut menu. I believe what you are suggesting will work, but it seems
like a fair bit of coding. I would also need also need to handle sorting,
know if it is ascending/decending for the orderby.

:

How exactly are you attempting to remove the subform filter? In VBA? Post the
germane bit of code if so.

Instead of manipulating filters, I usually simply add a criteria entry in
the RecordSource of the form and/or subform. Then, when the criteria changes,
all I have to do it requery the form or subform.

For example, if the main form has a single CustomerID and the subform has a
list of all that customer's invoices, the query for the subform's source
might look like this:

SELECT Invoices.* from Invoices where Invoices.CustomerID =
[Forms]![CustomerInvoices]![CustomerID]

If the CustomerID on the CustomerInvoices form changes, all I have to do on
the CustomerInvoiceSub form is Me.Requery, and it now shows all the invoices
for the newly-selected CustomerID.

:

I have a form with a subform. I can set a filter on the main form with no
problem. I can also set a filter on the values of a subform.

However, when I try to remove the filter from the subform the filter at the
main form is removed instead. The subform filter remains. So far the only
work around I have for this problem is to close the main form and start over,
not a desirable situation.

Does anyone have a solution for this problem? Thanks.