Limiting a subform datasheet combobox to values not in related table

[brokengod]

Dear genii,

I have a subform which allows users to create records against a given "client record" from a list in a table. In other words the main client form has a subform which shows a datasheet of "flags" which can be attributed to the client. Therefore one client can have many flags. I want to get the combobox of the datasheet to limit the options to those that are not already selected for that client ie. if a client already has the flags, say "pregnant" and "under 55", then these values would not be available in the datasheet to input the new record.

I have three tables for this, the tblClient, tblFlags (with all the available flag descriptions) and tblClientFlagLink which is a join table with 2 fields representing the client key and the flag key, selected together as the primary key. At present when a user attributes a preexisting flag to a client record, this violates the primary key rule, which is ok and prevents the data being input, but the subsequent warning is undesirable and for ease of use, I thought if I could remove the availability of the used flags, this would be smoother.

Hope this makes sense. Thanks in advance for any suggestions.

 

[PHV]

You may consider the NOT EXISTS predicate or an unmatched LEFT JOIN in the RowSource SQL code of the combo.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886