Code chokes when run within a Tab Control

[Micron]

IIRC when using that reference syntax you need quotes around the form name. You must keep the brackets because you allow spaces in your names. Many of us simplify code by not doing so.

[Kudos]

I guess you kinda did, but you referred to running the Events form in a tab control. That tab control must be on a parent form, so I knew the parent existed.

I can't really comment on the Member/Activities situation without more detail, but go with your gut and test out your guess! This page is an excellent reference for figuring out exactly how to refer to controls on forms and subforms: http://access.mvps.org/access/forms/frm0031.htm

Here's one way to test for the parent form - this is just checking if that particular form is open though, which is probably close enough logic for your purposes. I haven't tested this at all, just guessing at the code. Found here: https://stackoverflow.com/questions/...nt-form-exists

Private Sub Role_GotFocus()
Dim MyEvent As String

If Not CurrentProject.AllForms("Manage SCATeam").IsLoaded Then
MyEvent = [Forms]![Manage SCATeam]![NavigationSubform].[Form]![Event_Type]
Else
MyEvent = [Forms]![Events]![Event_Type]
End If

Me.[Role].RowSource = "SELECT [Role_Act].[Role], [Role_Act].[Ev_Type]" & _
" FROM [Role_Act]" & _
" WHERE InStr([Role_Act].[Ev_Type], " & MyEvent & " )" & _
" OR [Role_Act].[Ev_Type] = 'All';"
End Sub

And for your Contiguous Form/Parent Form issue, try: Set rst = Forms("Activities Ev Subform").Recordset

[WCStarks]

With my initial question related to this topic, I had attempted to use the Parent property of the sub-form, with which I was vaguely familiar. I think I had written it as Me.Parent.[Control], but Access choked on it. I am not sure now, but I may have included Form somewhere in the statement.

Then I found references to using explicit contexts to reference other forms found here.
https://support.office.com/en-us/art...6-6b24b5b14400
That led to this whole dialog of dealing with the full form hierarchy to reference a parent form.

As I was revisiting this topic, I thought there really should be a simpler way to reference a parent form, directly from the sub-form, with a relative reference. I looked up the Parent property, which works for both forms and reports. I saw that it can be used to reference the parent form of the sub-form directly from the sub-form, as in: Parent.[Event_Type]. Apparently, I should not have included the Me in my original attempt.

So, I replaced the complicated, hard-coded, full hierarchy expression in the sub-form control with Parent.[Event_Type], andit works as it should. Not only that, but it will work in whichever context I open the Events form.

[WCStarks]

Thank you.

[Kudos]

Oh that's much tidier. Very nice!