Filtering Subforms With Toggle Buttons

Question

I'm new to Access and I'm trying to filter a subform using a toggle button option group located on the subform. If I open the subform independently, everything works. When I use it as a subform of the main form, however, Access tries to filter the main form, and I can't figure out what I need to do to fix it. Ideally it would be nice to do this in VBA but I've used a macro simply because I don't have the time to learn VBA before this database has to be up and running.

Here are all the relevant parts:

• Main form: Search_by_name
• Subform: Action_Items
• Underlying table: tbl_Action_Items
• Subform control: sfctlActionItems
• Toggle button option group: tgl_Complete_Incomplete

And here's the macro as it stands right now:

If [tgl_Complete_Incomplete]=-1 Then

ApplyFilter

   Filter Name

   Where Condition =[tbl_Action_Items].[Completed]=True

   Control Name

Else

   RunMenuCommand

   Command RemoveFilterSort

End If

If I leave "Control Name" blank, Access asks for a parameter value for [tbl_Action_Items].[Completed] and tries to filter the main form. And no matter what I've tried to fill in for the control name, I get "The 'ApplyFilter' action requires a valid control name that corresponds to a subform or subreport." If I'm supposed to reference the control for the subform, then the control name should be [Forms]![Search_by_name]![sfctlActionItems], correct?

If I apply the filter manually to the subform using the built-in "Toggle Filter" button, everything works as it should.

What am I doing wrong?

Thanks.

Answer 1

I did actually use an option group with three buttons. The ElseIf statement worked, but your code looks like a more elegant solution. Thanks again.

Answer 2

I'm not sure how you would use a toggle button for three possible states. I would use an option group with three option buttons. Then use code with select case like;

Select Case Me.MyOptionGroup

   Case 1

   Case 2

   Case 3

End Select

Answer 3

That worked! Thank you so much, I've been trying for days to figure that out.

I realized earlier today that I do actually need a filter for incomplete. I had previously just been filtering for "complete" or "all." So would that be:

If Me.tgl_Complete_Incomplete = -1 Then

    Me.Filter = "[Completed]=-1"

    Me.FilterOn = True

  ElseIf Me.tgl_Complete_Incomplete = 0 Then

    Me.Filter = "[Completed]=0"

    Me.FilterOn = True

  Else

    Me.FilterOn = False

End If

Answer 4

You can try code in the after update of tgl_Complete_Incomplete:

If Me.tgl_Complete_Incomplete = -1 Then

    Me.Filter = "[Completed]=-1"

    Me.FilterOn = True

  Else

    Me.FilterOn = False

End If

This assumes the toggle button is on the subform that contains the data to be filtered. It looks like you don't have or need a filter for incomplete.