How to create unique combinations in a table using combo boxes

[Julian Stevens]

I have a continuous form in which the user is able to specify 3 fields
(Category, Transport and operator) using combo boxes. However, these
fields are used as the primary key for the underlying (bound) table.
Consequently, everytime the user adds or modifies one of these fields
I need to be able to populate the combo list with only those values
that will not create a duplicate record.
e.g. if my form contains:

Category Transport Operator
1. Air 747 Monarch
2. Air 737 Monarch
3. Air 737 Lufthansa

when I click on the the operator combobox for record 3, it shouldn't
show me Monarch, because I have that combination already. Similarly,
the transport dropdown for record 1 shouldn't show me 737, because I
already have that.

The transport values are stored in one table, with their associated
categories, and the operators are stored in a separate table.

I keep thinking I've nearly cracked it, but I'm still not there yet.
One problem I have is that, if you change the transport for one record
and then click the operator combo for the same record the selection
list is wrong because the underlying table has not been updated.
I'm also struggling to identify which event to use for the code that
changes the combo recordsource.

Any thoughts on this gratefully received!

Julian

 

[aday]

Julian:
The point of a primary key is that it must be atomic
and unique. Have you considered using a system autonumber
field for your primary key? That would leave your users
free to change the 3 fields thus making your job
considerably easier.

Hope this helps

 

[Julian Stevens]

Unfortunately, the requirements of the system are such that the table
must not contain more than one record with the same combination of
Category, Transport and Operator, hence the need to adjust the combo
box lists dynamically.

Julian.