I have a main form with two subforms whose data is pulled based on the selections in two combo boxes in the main form. For example, the selections may be "Home 1" and "January" and then the two subforms will requery their dynaset with those two selections.
My issue is in one of the subforms. Basically, subform #1 lists people who work in "Home 1" in "January", and subform #2 lists all the tasks that need to be assigned in "Home 1" for "January". When assigning tasks, the dropdown of people in subform #2 should only list the people that appear in subform #1. I've been able to get that to happen, but the problem is that certain people in subform #1 can only perform certain types of tasks (staff can only do x, shift supervisors can do x & y, and managers can do x & y & z). So I want the drop-down of the specific task to be filtered to only show the people that are allowed to do that task....ie, I don't want all the staff to show up on a task where only managers can do it. This will greatly reduce data entry errors.
I've gotten the drop-down to limit the items to what's in subform #1, and I've also got it to limit based on the task using an "on enter" code string in VBA, but whenever I go to a different task in the dynaset on subform #2, it will blank out the cells where the people that were selected in other records now don't meet the criteria of the new task, because the requery will requery all dropdowns for the entire subform datasheet. The interesting this is that the data doesn't get erased..it just disappears. This would just cause nightmares to the user who would think they've lost their work.
I've found that because the fields are linked based on a number (in the relationships) and because I don't show this number in the drop-down, it blanks out. But if I leave the number showing in one of the drop-down columns, then the number doesn't blank out when i move to another record and it requeries. So I've figured out that if I use a relationship based on the name, then it will stay, but I don't want to relate the tables based on names...and if I index the number with the name, the user is going to be confused by seeing both things in a string while they're selecting a person to do that task.
Any ideas on how to make a dynamic combo box that will let me store and show the names of the the people staffed for that task?
An alternative I've thought about would just be to have a small validation loop "On exit" of that cell, and giving an error message if they selected the wrong type of person, but that's my last resort.