Filtering Subform with Toggle Buttons

[doubleohkevin]

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.

[ranman256]

You DO filter the MAIN form to get the 1 master record.
This will automatic filter the subform records to it.

But if you want only to filter the sub-form data...

'Me is the main form

If [tgl_Complete_Incomplete].value Then        'true is implied
   sWhere ="[Completed]=True"
   me.chdSubform.forms.filter = sWhere
   me.chdSubform.forms.FilterOn = true  
Else
   RunMenuCommand   '??
   me.chdSubform.forms.FilterOn= false
End If

[doubleohkevin]

Thanks for the reply. So if I'm using your VBA code (that is VBA, right?) instead of the macro, do I start the code with the default that Access gives me (Private Sub Tgl_Complete_Incomplete_AfterUpdate())? And do I replace "chdSubform" with the name of the subform control or something else?

Here's what I'm trying to do, exactly: I have the contact's information on the main form. The subform contains action items associated with the contact. When the subform is filtered, it will display only those records that are marked complete (using a Yes/No field in the underlying table). The toggle button control to display either all records or only completed records sits in the subform, so nothing on the main form gets filtered, and there are no controls on the main form associated with the filter.

[doubleohkevin]

Someone pointed me to a nice simple solution on another forum:

If Me.tgl_Complete_Incomplete = -1 Then
    Me.Filter = "[Completed]=-1"
    Me.FilterOn = True
  Else
    Me.FilterOn = False
End If

Thanks again for the suggestion. I appreciate it.