Tricky Values in a Combo Box

[vt800c]

This one has me stumped to the point I'm not sure WHERE to begin:

I have three source data tables: a 'program', an 'organization' and a 'role' that a person can have within that program and organization.

Some roles allow multiple members (like programmers) whereas some roles allow only one (for example, the Team Lead).

So here is my delimma:



If I want to assign an individual to a specific program and organization, The role choices that I want in the combo box are roles not occupied. I don't want to use 'flags' to say a position is available, because of the number of roles, programs and organizations.

I should be able to limit the choices in the list box to the roles that are 'available'.

Remember, some roles allow as many individuals as I want, other roles have to be vacant.

[vt800c]

ok..I have a query (based on the values selected in ComboBoxes on a form) that returns the values of the 'privilidged' roles that are filled, based on the program and organization.

Can I turn the rows into a 'not in list' type of where criteria for the record source for the combo box?

So if the query says 'someone is filling role_id 4', the role associated with role_id 4 is not a choice. But all other role-IDs are available.

Remember the query can return multiple rows (3 and 4) if those restricted Role_IDs are filled. Those choices should not be available, as they are already filled.

[orange]

There is a free video here that deals with the concept of showing a value in one box or another. Basically there is a list of Available things, and a list of Already Taken things.
The video shows how to set it up and how to move things from one list to the other.

It's well work the 5 minutes to see how it's done, and you may be able to apply the concept in your case.

http://www.datapigtechnologies.com/f...tboxtrick.html

[vt800c]

Thanks for your suggestion, Orange. I will see if there is a way I can use the data structures I already have to implement your idea. It may be that I need to make an intermediate query that displays only the values desired, and have that as a dynamic recordset that is requeried on each time a 'controlling' value on theparent form is changed. Let me give that some thought...

[vt800c]

forget it...I handled it another way, with checking the selected value in the BeforeUpdate event of the field. if it's a restricted role that is already filled, I'll display a messagebox and cancel the change. Simple, and it works.

[orange]

Thanks for posting that you got it resolved.