Combo Box Question

[beaverx37]

I have a camp database. I have counselors and campers. I have a camper attendance table and a counselor attendance table. On both tables you can select which camp they are registering for. In my camper attendance form the staff can select which counselor the camper is assigned to. I have this set up and working.

This is what I need. I want to be able to (after I have selected that counselor Johnny will be at march camp 2015 but counselor Bob will not be) select that camper Frank will be attending march camp 2015 (also already set up) and when I select the drop down box to choose his counselor, it only display counselor Johnny and not counselor Bob.



Is this possible? If so how would I do this, and where would the code to do it go?

Thanks!

[ryanmce92]

I had the same problem trying to do this with a supplier database, I found this video helped me a lot, correct me if I'm wrong but I think that process is called cascading combo boxes. See the video in the link and hopefully this is what you mean

http://www.youtube.com/watch?v=SpMyGlEInGs

So you would have March in the form before you select the councillor and once the month is selected only the councillors available in that month will appear?.

[NTC]

what you want to do is not necessary - fundamentally speaking - if 1 single Counselor is entered in a db for a Camp - then every Camper in the same Camp defacto has the same Counselor. It really is a matter of display - and you join those together in a query for a report or form display but not part of the form that is assigning Campers to a Camp. Much like assigning a student to a class. If the teacher of that class is in the db - then you don't have to bother assigning the teacher to the student.

but perhaps your post was not entirely accurate and it is not a single Counselor for the Camp session but a set of them; and you do have to choose among a sub set of Counselors. The combobox of Counselors is based on a record set and that record set can be a query, rather than the whole table. That query can be set up with criteria based upon the camp session. If your Camper assignment form is going to be choosing among Camp sessions then after the Camp session is selected you must trigger the Counselor combobox to requery in order for it to be using the criteria of the Camp session just selected.

[beaverx37]

what you want to do is not necessary - fundamentally speaking - if 1 single Counselor is entered in a db for a Camp - then every Camper in the same Camp defacto has the same Counselor. It really is a matter of display - and you join those together in a query for a report or form display but not part of the form that is assigning Campers to a Camp. Much like assigning a student to a class. If the teacher of that class is in the db - then you don't have to bother assigning the teacher to the student.

I was taking what I needed and simplifying the issue just for explanation sake, I didn't see the point in listing all 10-20 counselors out of 30-40 possible counselors just to explain what I needed. But thank you both, I think the video ryanmce92 gave me the link to is just what I need.