Remove Filter/Sort

[jknow]

When removing filter with icon on toolbar, main form filter is removed but
filter for combobox is not removed.
When I first set the filter I get an ApplyFilter event with ApplyType = acApplyFilter. When I press the filter toolbar button again to remove the filter I get the ApplyFilter event with ApplyType=acApplyFilter again, when I expected it to be acShowAllRecords.

If I remove the filter using the equivalent menu command,
filter is removed from both. Then I do get ApplyType=acShowAllrecords.

I have tried using
Me.Filter = ""
Me.FilterOn = False
at various times and in various places, but the form believes the filter is off (and looking at the main form - all records are shown), but the combobox still has the list filtered.

I have to check the filter and whether it is on in order to build my SQL statement that is the source for my combobox. There is a main form with four subforms on it. After update of the combobox, the code goes through form_current of all the forms until it has finished and is on the correct record. (The combobox is used as a quick search tool - using recordset.clone and .bookmark). It may go through form_current of the main form several times before it has completed this iteration.

As I said above, all works well if I use the menu command, but if I use the filter icon on the toolbar, the combobox and form are out of sync and the form locks up if you try using combobox again. If I move to another record using the record selectors, all is well again and filter is removed from combobox.

Can anybody explain this behavior, or offer a workaround ?

 

[CyberLynx]

There is no Filter for a Combo Box. You must mean the RowSource property.

When a Filter is applied to a Form, the OnCurrent event is fired. It will be there that you need to make the necessary changes for your Combo Box RowSource property.

For example:

If Me.Filter = "whatever" Then
   Me.myComboBox.RowSource = "whatever query to populate Combo"
Else
      Me.myComboBox.RowSource = "whatever Other query to populate Combo"
End If

.

 

[jknow]

You're right. That is what is being done. When the filter is removed via the icon, the code steps through the form_current, checks to see if there is a filter or sort on, then applies the appropriate SQL code. The problem is that it thinks the filter is still on.

It was just brought to my attention that when the menu command is used, it basically requeries the information, where as the icon appears to just refresh it. I don't know if that has anything to do with it or not.

 

[CyberLynx]

Try using a form to apply your Filter(s). I personally never use the Filter in menu (don't even like looking at it). I find better control via my own Filter mechanisms and Code.

.