In a form/subform situation, I have 4 fields on a form in which the user enters data. On the subform, I want the user to populate a field based on the data entered in the 4 fields on the form using a combo box or some sort of drop-down list.
I designed a database similar to your question. I had to use dlookups and requery functions in macros and on the forms to limit a users drop-down list based on previous entered information in the form. The requery takes care of the issue if they go back and change their original choices, it changes the drop-down lists. If you need more specific info, let me know and I'll see what I can send to you. If the dlookups and requerys aren't in the right spots on the form and macro, it will not work.
The example I'm sending you is when I wanted to limit the choice of description items based on what the user selected as a Business Need. I hope this information helps. For a better description of the elements in the Dlookup, see the Help file. It will explain what each section is.