As far as having a global Is_Active variable you could do just that. Define a global variable that the checkbox after_update would set to true or false.
Then all the forms with members as recordsource would need to have something like
Select * from Members where Status = Is_ActiveGlobal. Instead of a global you could use a tempvar.
Also, all the comboboxes that select a member would need to have the rowsources modified to check the global.
In some cases, you don't want a combobox to show an inactive member regardless of the global so criteria would be Where Members.Status = True
Change the ManageSCATeam code to this so that at BOJ it honors the Active checkbox.
Code:
Option Compare Database
Option Explicit
Private Sub Form_Load()
Call Is_Active_AfterUpdate
End Sub
Private Sub Is_Active_AfterUpdate()
Select Case Is_Active
Case True
Me.RecordSource = "select * from members where Active = true"
Case False
Me.RecordSource = "members"
End Select
End Sub
Then the SCATeam form_current needs to change to this, as the textboxes are unbound and will keep any previous value going from record to record unless explicitly changed. So for new records the previous value was shown. Code fixes that.
Code:
Private Sub Form_Current()
If Me.NewRecord Then
Me.Joined = vbNullString
Me.Resigned = vbNullString
Exit Sub
End If
Me.Joined = ELookup("[Status_Date]", "Membership", "[Status] = ""Joined"" and [Member_ID] =" & [ID], "[Status_Date] Desc")
Me.Resigned = ELookup("[Status_Date]", "Membership", "([Status] = ""Resigned"" or [Status] = ""Terminated"" or [Status] = ""Emeritus"") and [Member_ID] =" & [ID], "[Status_Date] Desc")
End Sub