By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,995 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Combo Box Progressive Requery

P: n/a
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
Dec 28 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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=tblSub.FieldA
WHERE tblSub.FieldA=NULL
ORDER BY fieldA

Dec 28 '05 #2

P: n/a
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********@hotmail.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.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=tblSub.FieldA
WHERE tblSub.FieldA=NULL
ORDER BY fieldA

Dec 29 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.