How to write code for filter button? (MS ACCESS 2010)

Question

Is it possible to create a filter button on a form that shows record for the [datanumber] from 1 to 20? I tried searching for one but no luck. I don't know if using a macro or writing an event procedure is the right way to do it. Can someone give me an example of how I should write the code? I want to filter by [datanumber] by clicking a button on the form. Thanks

Answer 1

Then you will need to reference the subform from the main form (UNTESTED)....

If [NameOfYourSubform].Form.FilterOn Then

    [NameOfYourSubform].Form.FilterOn = False

Else

    [NameOfYourSubform].Form.Filter = "datanumber <=20"

    [NameOfYourSubform].Form.FilterOn = True

End If

---

--

Gina Whipp

2010 Microsoft MVP (Access)

Please post all replies to the forum where everyone can benefit.

Answer 2

The following in the button's Click event procedure will toggle the form between filtered for the datanumber being 20 or less, and unfiltered to show all records:

If Me.FilterOn Then

    Me.FilterOn = False

Else

    Me.Filter = "datanumber  <=20"

    Me.FilterOn = True

End If

For a more flexible approach you could add two unbound text box's to the form, cboFrom and cboTo, each with a RowSource property of:

SELECT [datanumber] FROM [YourTableName] ORDER BY [datanumber];

The buttons code would then be:

If Me.FilterOn Then

    Me.FilterOn = False

Else

    Me.Filter = "datanumber  >= " & Me.cboFrom & _

        " And datanumber  <= " & Me.cboTo

    Me.FilterOn = True

End If

This would enable you to select the range from the two combo boxes and then click the button to filter the form.

---

Ken Sheridan, Stafford, England

Answer 3

Note that in Gina's code NameOfYourSubform is the name of the subform control in the parent form. i.e. the control which houses the subform, not the name of its underlying form object, unless both have the same name of course.

---

Ken Sheridan, Stafford, England

Thanks, it works perfectly.

Answer 4

Note that in Gina's code NameOfYourSubform is the name of the subform control in the parent form. i.e. the control which houses the subform, not the name of its underlying form object, unless both have the same name of course.

---

Ken Sheridan, Stafford, England

Answer 5

The following in the button's Click event procedure will toggle the form between filtered for the datanumber being 20 or less, and unfiltered to show all records:

If Me.FilterOn Then

    Me.FilterOn = False

Else

    Me.Filter = "datanumber  <=20"

    Me.FilterOn = True

End If

For a more flexible approach you could add two unbound text box's to the form, cboFrom and cboTo, each with a RowSource property of:

SELECT [datanumber] FROM [YourTableName] ORDER BY [datanumber];

The buttons code would then be:

If Me.FilterOn Then

    Me.FilterOn = False

Else

    Me.Filter = "datanumber  >= " & Me.cboFrom & _

        " And datanumber  <= " & Me.cboTo

    Me.FilterOn = True

End If

This would enable you to select the range from the two combo boxes and then click the button to filter the form.

---

Ken Sheridan, Stafford, England

Actually, I'm trying to put a button on the mainform that filters what is shown on the subform. Is this still the same code? I can't get it to work. It always ask me to specify what datanumber is. Thanks.