ComboBox Progressive Requery

[Robert]

Thanks and would like to use your suggested join approach however
tblSub.FieldA is one field of a two field key (2nd key field say FieldB) for
tblSub. So need to exclude tblX.fieldAs in the combo box list only where
there is a saved record in tbl.Sub with the same tblSub.FieldB

Can you help with the join in this case where null case needs to be for both
FieldA and FieldB (or a combined field)

<pi********@hot mail.com> wrote in message
news:11******** *************@g 47g2000cwa.goog legroups.com...

use an outer join.
Say the datasource for the subform is tblSub
Also the rowsource for the combobox is something like
SELECT fieldA
FROM tblX
ORDER BY fieldA;

so you just change it a little...
SELECT fieldA
FROM tblX LEFT JOIN tblSub ON tblX.FieldA=tbl Sub.FieldA
WHERE tblSub.FieldA=N ULL
ORDER BY fieldA

Original post....

Am using a nested continuous bound subform to add multiple records to the
underlying table. One of the fields is based on a limit to list combo box.
Any suggestions on best way to progressively update the combo box query so
that for each new record being entered the combo box list excludes items in
the combo box list previously selected.

Aim is to prevent user from selecting the same combo list item more than
once if the user has already added a record previously using that list item.

The combo box field is part of the primary key for the underlying table and
am currently trapping the duplication error and then getting the user to
re-select a different combo box list item but would prefer removing the
previously selected & saved combo list item from the pick list.

This would allow me to also include logic to exclude mutually exclusive
items as well from the pick list.

Example in summary.

Combo box initial pick list items
A
B
C
D

To Avoid duplication - if user picks B and record is then saved, pick list
for the next record on the continuous subform becomes
A
C
D
For Mutually exclusive items - want say A & C to be mutually exclusive - if
user picks C and record is saved, pick list for the next record becomes
D

Any suggestions on best way to handle updating the selection criteria string
appreciated.

TIA, Bob