I did try your modification in post 42, but it does the same thing. It appears that the query does not get modified initially, but retains the active status, until after I leave the Members tab and come back to it. Then, I can switch back and forth at will, with no trouble. It is just after first opening the database with the Members form active and switching the Active Status off that it doesn't work. It will only find Active members regardless of how the Active status is set, and as many times I switch the status. I must first leave the Members form and return to it, before it will begin to work correctly. Here is the code I copied from the v03 db in post 42. I have since moved the called Function to my UDF module, thinking that might make a difference, but it did not. Notice how I modified the non-active code to include both active and non-active when Active is False.
I have no idea how to get this to work.
Code:
Private Sub Is_Active_AfterUpdate()
' By Davegri on Access Forum
Dim ssql As String
Select Case Is_Active
Case True
ssql = "select * from members where Active = true"
Case False
ssql = "Select * from Members"
End Select
Call fcnMakeNamedQryFromSQLString(ssql, "qry_MembersSelected")
'below will set the recordsource for each navigation subform as the buttons are clicked!
'the previous subform is UNLOADED!
DoEvents
Select Case Me.NavigationControl0.SelectedTab.Name
Case "nbMembers"
Me.NavigationSubform.Form.RecordSource = "qry_MembersSelected"
Case Else
Forms![Manage SCATeam].NavigationSubform.Form.Refresh
End Select
End Sub