Filter by Active in Combobox with OptionGroup

[WCStarks]

I have a database of members who are either active or not active.
I have a combo-box which allows me to select members to view.
I need to have the combo-box show members in the list who are either only active members or All members.
I have a checkbox in the header of the parent form called [Is_Active], which allows me to filter on Active or not.
I have an Option Group on the Member Sub-form that allows me to select sort order for the Combo-box by name or Call Sign.

Combo-box SQL sets the default structure and behavior of the Combo-box:
SELECT Members.ID, Members.Mem_Name, Members.Call, Members.Active, Members.Active
FROM Members
WHERE (((Members.Active)=[parent].[Is_Active]))
ORDER BY Members.Mem_Name;

This Combo-Box WHERE clause works correctly initially, but it no longer works after I toggle the Option Group sort order selections.
The Option Group On Click Event Procedure:
Private Sub Find_Sort_Click()
Select Case Find_Sort
Case 1 'Sort by Name
Me.[Combo106].RowSource = "Select [Members].[ID], [Members].[Mem_Name], [Members].[Call], [Members].[Active]" & _


" FROM Members" & _
" Order By [Mem_Name];"
Case 2 'Sort by Call Sign
Me.[Combo106].RowSource = "Select [Members].[ID], [Members].[Mem_Name], [Members].[Call], [Members].[Active]" & _
" FROM Members" & _
" Order By [Members].[Call];"
End Select
End Sub

I tried adding a WHERE clause to both cases, as in:

Private Sub Find_Sort_Click()
Select Case Find_Sort
Case 1 'Sort by Name
Me.[Combo106].RowSource = "Select [Members].[ID], [Members].[Mem_Name], [Members].[Call], [Members].[Active]" & _
" FROM Members" & _
" WHERE Me.[Combo106].Column(3) = Parent.[Is_Active]" & _
" Order By [Mem_Name];"
Case 2 'Sort by Call Sign
Me.[Combo106].RowSource = "Select [Members].[ID], [Members].[Mem_Name], [Members].[Call], [Members].[Active]" & _
" FROM Members" & _
" WHERE Me.[Combo106].Column(3) = Parent.[Is_Active]" & _
" Order By [Members].[Call];"
End Select
End Sub

But then, when I attempt to open the Combo-box, after making a sort order selection, I get the following error:
Undefined function 'Me.[Combo106].Column' in expression.

How can I make the filter work after I change the sort order of the combo-box? Refer to the image of the relevant portion of the forms.

[Micron]

So you have a checkbox control on a subform and depending on its state, you want to modify the list of a combo box? Your problem is likely an incorrect subform reference. To reference a subform control from main form code requires a syntax like
[Forms]![Main form name]![subform control name].[Form]![control name on subform]
where [subform control name] is the name of the CONTROL that contains the subform. It may or may not have the same name as the subform (it should not as a matter of good design practice, but still should work).

Admittedly, I glossed over much of what you posted. Aside from me having to log out (gotta run), not using code tags for more than a few lines of code makes it harder for us to read. Plus, it's hard to read through all that info first without any clue as to what the problem is. The point being, as we're trying to decipher it, we have no idea what to look for because we don't know what the issue is. So to help us help you, use code tags, state what the problem is, then expand on the setup. You did great in providing background info - it's just hard to read through it.

BTW, the suggestion is based on the assumption that there is not a navigation form involved here, which can change things if that's the case. The picture might be that of a tab control but I can't tell.

[WCStarks]

I will try to describe the issue more succinctly and clearly.
0) I want to select members in the Combo-Box, filtered by the member's Active status, or unfiltered.
1) The main form, "SCATeam", contains a set of Navigation tab forms. The check-box [Is_Active] is in the header of this form.
2) The Sub-form is one of the navigation tab forms, Members.
3) The combo-box on this sub-form allows me to select a member to go to and view. The reference to the [Is_Active] check-box in the main form, is from the Members sub-form. This reference works correctly, initially. When I toggle the [Is_Active] check-box, the list changes from filtered to unfiltered, back again, with each toggle selection.
Combo-Box Data property
SELECT Members.ID, Members.Mem_Name, Members.Call, Members.Active, Members.Active
FROM Members
WHERE (((Members.Active)=[parent].[Is_Active]))
ORDER BY Members.Mem_Name;
4) However, after, I change the sort order for the combo-box, by making a selection in the related Option Group control in the sub-form, the Active filter no longer limits the selection in the combo-box. I can toggle the [Is_Active] control, back and forth, to no avail. It reverts back to all (no filter). See the Option Group code in my original post.
5) I tried adding a WHERE clause in both CASE statements in the Option Group control, but that produces the error mentioned in my original post, when I attempt to open the combo-box.

[Micron]

I'm not being much help today because I was just checking the status of this and have to leave in 5 minutes. So it seems there are 2 possible issues,
" WHERE Me.[Combo106].Column(3) = Parent.[Is_Active]"
doesn't look right. It should resemble standard query practice as in WHERE myTable.myField = True " when it's checked, and False when it's not.

The other issue would be that if what you have is OK, you are not requerying the combo after you alter its row source sql as in

End Select
Me.[Combo106].Requery
...
End Sub
Hope that helps, or that someone else can jump in before I can get back to read over your posts in more detail.

[WCStarks]

Thanks for your response.

I added the Requery statement and played around, but I was still was not getting it to work. Whenever I included the Where statement in the Option Group On Click code, I would get the error mentioned originally. I finally went back and looked at the Data property of the Combo-box. I realized that it made a direct reference to the Members.[active] field. So, instead of referencing the Combo's Column(3) in the Where statement, I decided to try referencing the Members.[Active] field in where statement. See below. This works, sort of.

Now, when I change the parent.[Is_Active] checkbox, it doesn't take affect in the Combo-box, until after I toggle the Option Group. So, each time I toggle the parent.[Is_Active] checkbox, I also need to toggle the Option Group at least once, and, perhaps, again to get the desired sort order. Only then, will the combo-box list reflect the new parent.[Is_Active] status.

Private Sub Find_Sort_Click()
Select Case Find_Sort
Case 1 'Sort by Name
Me.[Combo106].RowSource = "Select [Members].[ID], [Members].[Mem_Name], [Members].[Call], [Members].[Active]" & _
" FROM Members" & _
" WHERE ((Members.Active) = Parent.[Is_Active])" & _
" Order By [Mem_Name];"
Case 2 'Sort by Call Sign
Me.[Combo106].RowSource = "Select [Members].[ID], [Members].[Mem_Name], [Members].[Call], [Members].[Active]" & _
" FROM Members" & _
" WHERE ((Members.Active) = Parent.[Is_Active])" & _
" Order By [Members].[Call];"
End Select
[Combo106].Requery
End Sub

When I originally created the Combo-box, I let the Access Wizard do it for me, and it created an After Update Macro, instead of an Event Procedure.
Macro SearchForRecord
Record: First
Where Condition =: ="[ID] = " & Str(Nz([Screen].[ActiveControl],0))
I added the ":" to delimit the Labels.

I suspect the problem may be with this very abstract Macro, which I would not have know how to produce myself. I tried adding a Macro Requery Action to this Macro, and as a separate Macro in the Before Update, and the On Click properties, but none of those did any good.

Technically, the logic of the Where clause is not quite right. I need to change it, to produce either a list of Active members, or a list All members. As written, the list toggles between Active and InActive members. But that is easy enough to fix, after I get the list toggling as it should.

[WCStarks]

Expanding on your suggestion to re-query the combo-box, I solved the problem by Creating an Event procedure for the Control's On Got Focus property, which just re-queries the combo-box.

Private Sub Combo106_GotFocus()
Me.Combo106.Requery
End Sub

[Micron]

Glad you got it working. If you want me to take a crack at fixing your code, compact/repair your db and post a zipped copy of it. I find that following your posts has been difficult. Not sure if it's because it's been hard to decipher what's what and where it is located, or if it's just because I've not been able to spend enough time on your problem in one sitting. It seems I've been quite busy the last couple of days.

Suggest you bookmark this page as Parent is a reserved word and shouldn't be used for an object name. At first, I thought it was a reference to the hierarchy of your forms. Only now did I realize it's probably the name of a field, so that and your lack of code tags (I see you still haven't conceded on that) didn't help me either. Sorry that someone else didn't jump in here to expedite a solution - usually someone does.

[WCStarks]

Thank you for your help. I am sorry my descriptions are so difficult to follow. Actually, Parent has just been used to reference a parent form of a form. It was not the name of an object. I noticed that Access had bracketed some of my Parent references in the code, which may have caused some confusion. For example, under the Events Navigation Tab form's continuous sub-form, Assignments, I modified the code to replicate what I had done under the Members navigation tab:

SELECT Members.ID, Members.Mem_Name, Members.Call, Members.Active
FROM Members
WHERE (((IIf([Parent].[Parent].[Is_Active]=True,((([Members].[Active])=[Parent].[Parent].[Is_Active])),True))<>False))
ORDER BY Members.Mem_Name;

In this case, this Assignments continuous sub-form is another level down from the top form. I didn't bracket these parent references. Access did it for me from the Design view. In the last parameter of the IIF() function, I also entered "=True", and access changed it to "<>False". I don't know why it bothered to do that.

This Assignments form also had a similar Option Group control with similar code, as under the Members tab form. The SCATeam main form, with the Is_Active checkbox is always at the top level. So, that control is always available to the sub-forms, similar to a global variable.

I not sure what you mean by code tags.

My above references to the Events form and its sub-form is probably hard to follow, so here is the image of the Events/Assignments forms.

[WCStarks]

Micron,
Since I wasn't aware of Code Tags, I searched Code Tags yesterday evening, and found out what they were. In the future, I will use them. Sorry. One thing that is frustrating for me is, I like to indent my code to make it more readable, but the Posting editor removes all my indents when I post the dialog. I understand others would also like to see the code indented. How does one get the indents to remain after posting?

[Micron]

So you discovered one good reason to use code tags - indentation, the lack of which makes code harder to read. Another is that without the tags, the forum will add a space to your code after 50 continuous characters, which often elicits comments about errors in the code when there is no such error. Code tags (pound/hashtag button on menu bar) will usually automatically indent your code. The great advantage of using the tags is that they will hold whatever indentation you apply, and from my experience, will often add to it. So I'd advise using "Go Advanced" to preview your code layout because if you put 4 space indentation on your lines, it will probably be too much. I find that 2 spaces is often best IMHO, but I always check it to ensure additional spaces were not added. See post 5 - this is your code without tags.
This is your unmodified code with tags (i.e. I did not alter any indentation)

Private Sub Find_Sort_Click()
Select Case Find_Sort
Case 1 'Sort by Name
    Me.[Combo106].RowSource = "Select [Members].[ID], [Members].[Mem_Name], [Members].[Call], [Members].[Active]" & _
    " FROM Members" & _
    " WHERE ((Members.Active) = Parent.[Is_Active])" & _
    " Order By [Mem_Name];"
Case 2 'Sort by Call Sign
    Me.[Combo106].RowSource = "Select [Members].[ID], [Members].[Mem_Name], [Members].[Call], [Members].[Active]" & _
    " FROM Members" & _
    " WHERE ((Members.Active) = Parent.[Is_Active])" & _
    " Order By [Members].[Call];"
End Select
[Combo106].Requery
End Sub