I have two tables, Events and Activities. The child table, Activities, associates members with events. I have a parent form for the event and a sub-form for the activities.
I have a "Role" combo box, in the sub-form, to select from a rather long list of Roles obtained from a table of Roles. These roles are associated with participation in an event defined in the parent Event Form. The Event is defined by a Combo box called "Event Type". It would be helpful to filter the long list of Roles in the child sub-form, based on the Event Type selected in the parent form.
The Role Table record currently includes an ID and a Role.
I am thinking of adding a 3rd column to associate a set of Roles to an Event Type it would be used with, such as:
[ID, Role, Event Type]. Then have the Role Combo Box filter its list to include only those Roles associated with a certain Event Type value, while not displaying the Event Type in the list.
I can almost think my way through it, but not quite. Any Ideas would be appreciated.