Access/VBA dynamic combo box w/ subform in Datasheet view

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, 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 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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

A common problem of a combobox in a continuous form or datasheet view where the combobox's row source property changes. The problem is there is  really only one combobox that shows up many times. The trick is to place a textbox on top of the combobox. For the solution, take a look at:  Report Form Tricks  Changing combobox in continuous forms
rczubaAuthor Commented:
I've thought about doing that, but I'd have to change the subform to continuous form, which I actually prefer.....the issue is that this doesn't allow for the user to scroll up and down, left and right between records, as you can in datasheet view.  This is critical for the user as the may assign 12 consecutive tasks to the same person, so they'll just type "L", hit the down arrow to go to the next record, type "L" and hit down, and so forth....unless you know how to get the arrow keys to work on a continuous form...
>unless you know how to get the arrow keys to work on a continuous form...<

Yes, Use this code:

Private Sub cbaYourComboboxNameHere_KeyDown(KeyCode As Integer, Shift As Integer)
On Error GoTo TheExit
Select Case KeyCode
    Case vbKeyDown
        DoCmd.GoToRecord , , acNext
    Case vbKeyUp
        DoCmd.GoToRecord , , acPrevious
End Select
Exit Sub
KeyCode = 0
End Sub

I added that to the sample.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rczubaAuthor Commented:
Sorry for not responding sooner.  I was out of town for a few weeks.

Back to the issue at hand, I tried the text box, and it works, but the problem is that the source field is just the ID field that ties back, and I need it to display the name associated with the ID.  So I tried doing a dlookup in the textbox that pulls the name based on the id and it works fine, but it's slower and a bit more clunky.  
>the problem is that the source field is just the ID field that ties back, and I need it to display the name associated with the ID.<

Relate the two tables in the form's record source query and have the query supply the name.   If you need help with that, post the forms record source query and  your DLookup.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.