Hello fellow Access users,

I am trying to filter a form using a checkbox.

The filter trigger I need to be a checkbox in the header.

I thought something like this might work:

If Me.[Checkbox in header] = True Then
Me.Filter = [Field in form] = True
Me.FilterOn = True
Else
Me.FilterOn = False
End If

But to no avail. Can anyone please help? Many thanks!

I believe the Filter needs to be a string so try: Me.Filter = "[Field in form] = True"

Okay, you want to filter on a field whose value can be true or false?

I take that assumption from the line in your code,

Me.Filter = [Field in form] = True (which should be written, as you've advised, Me.Filter = "[Field in form] = True")

Instead of a separate, additional, checkbox, which only duplicates that value (yes/no), why not just toggle the filter on or off directly in the After_Update value of [Field in form]?


In fact, you can write the code for it this way:

Private Sub Field_in_Form_AfterUpdate()

Me.Filter = "[Field in form] = True"
Me.FilterOn = Me.[Field in form]

End Sub

That is assuming, of course, that the value of [Field in form] is, in fact, true or false.

If you are trying to evaluate something else, please post back with more details.

George

Thank you very much for this.

I would now like to be able to filter a form based upon a number of different toggles.

eg using what you have provided
Me.Filter = "[Field in form] = True"
Me.FilterOn = Me.[Field in form]

but for 10 fields on the form! they are all yes/no's. basically i want to be able to build on a filter so it keeps on filtering the records.

I fear I may need a complicated If statement which Im not sure how to do.

Whoever may be able to help I will certainly be more than grateful.

Regards
Elise

I would build an option group with 11 options...the first 10 being your fields, the last being "Clear Filter."

Assign values of 1-11 to each option, and name the option group "optFiltSelect" in the Name property on the properties sheet.

In the option group's On Click event, have the following code:

Select Case me.optFiltSelect.value

case is =1
me.filter="[Field1]=True"
me.filteron=true
case is =2
me.filter="[Field2]=True"
me.filteron=true
...
case is = 10
me.filter="[Field10]=True"
me.filteron=true

case is =11
me.filteron=false
end select

Obviously, you'd have one Case is = statement for each field, and you'd use real field names.

HTH
Ben

The term for yes/no values like this is "boolean". Booleans can only have one of two values: yes/no, on/off, true/false, -1/0, or whatever.

Because checkboxes are boolean, they don't lend themselves well to multi-part filtering like this without, as you say, a lot of If statements. Moreover, the number of If's required is going to be exponential, I think. Each time you add a checkbox, the number of possible combinations of "if's is going to double.

Actually, I think there might be a way to do this, but I don't have time right now to write it all out. Maybe tomorrow.

Also, there might be a better way to attack this problem. How about posting up your db so someone can take a closer look?

George