ApplyFilter Event didn't work as expected

[seeking]

Hi,

I have form containing a combo box in FormFooter. I noticed than after I open and close the "Filter by Fom" window that combo box is always set as Null, which is not I want. I want to keep the value. So I tried to ApplyFilter to reset the vaule.

Here is my code:

Private Sub Form_Current()

varCtrl_test = [test]

End Sub

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

[test] = varCtrl_test

End Sub

[test] is the name of combo box and varCtrl_test is a global variable.

The value of [test] is saved into varCtrl_test and then [test] would be reset after applying filter. But it didn't occur in that way: [test] is still set as Null as usual.

I am sure the ApplyFilter event procedure is called. And more confusing is when I move that combo box from FormFooter to FormHeader then everything works well.

Is there anyone can cast a light on it?

 

[seeking]

By the way, I am using Access 97.

 

[vbaInet]

I guess what's happening there is the value of the combo box is dependent upon the records in the Detail section?

 

[seeking]

I guess what's happening there is the value of the combo box is dependent upon the records in the Detail section?

The value of combo box is input by user. The a query is run using that value in WHERE clause to give the records in the Detail section. Does this matter? If so, how can I sovle it?

 

[vbaInet]

Let's have a look at your db.

 

[seeking]

Let's have a look at your db.

How can I post the db here?

 

[vbaInet]

I forgot you need at least 10 posts to be able to do so. Until then, because the records are feeding off from the Combo box and you're using Filter By Form, I don't think there's a way to leverage that unless you build your own search form.

Why don't you want it to be in the Header section if it's ok there?
What happens if you put it in the Page Footer section? I think it will behave the same but worth a try.

 

[seeking]

I forgot you need at least 10 posts to be able to do so. Until then, because the records are feeding off from the Combo box and you're using Filter By Form, I don't think there's a way to leverage that unless you build your own search form.

Why don't you want it to be in the Header section if it's ok there?
What happens if you put it in the Page Footer section? I think it will behave the same but worth a try.

The value of combo box is used as a criteria in query. Therefore it may be more natural to put it in FormFooter along with orther controls. I have thought there should be no differenc. But it turned out not.

Here is the what I do with the combo box: Input the vaule in combo box; then click on a button to create a query using that value in WHERE clause; and then use that query as RecordSource of the Detail section.

You can see that the Filteron property is indeed False after this. I only want to keep that value after using Filter By Form. The behavior is quit confusing.

 

[vbaInet]

Not in most dbs it isn't. It is often the case that filter selections are put in the header section of the form and totals are calculated at the footer. I've not come across a db where the filters are at the bottom but I've seen them on the side.

It is logical that since the header section is normally used for this purpose the Filter By Form shouldn't have any effect on the controls placed in that section.