Combo boxes

[Design by Sue]

I a form on which that I can assign up to 3 job descriptions for one employee. The form has 3 combo boxes that link to the same field in the table that lists the possible job descriptions. How do I limit the choices in the 2nd combo box to those NOT selected in combo box 1 and then in combo box 3, limit the choices to those NOT chosen in combo boxes 1 and 2?

Thanks
Sue

 

[andyeastes]

you could have the data source be queries for combo 2 and 3 where criteria is not like and reference the previous combo boxes.

 

[Design by Sue]

That worked almost. It works based on the first record on the form , but when I go to the next record, it is still giving the drop downs choices from the first record. It is not update the list for the new record.

Sue
(edited because I though it worked but then found this bug)

 

[andyeastes]

So now you are saying the second combo box works filtering out the selection from combobox1 but when you go to combobox3 it is not filtering out seleciton from combo2? What is the SQL code for the query combo3?

 

[Design by Sue]

No that's not what I was trying to say - it was between records (from one employee to the next) but I'm getting closer by using a code to requiry the combo boxes. I did a requery of box 2 and 3 "on change" of box 1 then requery of box 3 "on change of box 2. That got the boxes to work if the user makes the selections, but if the record is visited again, the combos still show the choices from the previous employee. I am now working on a requery of the 3 combo boxes on "got focus" of the form, but I think I just lost the results in combo 3. I will keep poking at this but if you can even understand this and have a solution it would be appreciated!

Sue

 

[andyeastes]

You can simply close and reopen the form and user cannot even tell it happened or is this a subform?

 

[Design by Sue]

I think I might have it - I put the requery of all 3 combo boxes in the "on current" event of the form - it seems to work now. I'll continue to beat at it to be sure. Thanks for you help.