Limit a list box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

access 2003
Each person's record has a primary and secondary choice of assignments

Primary choice field looks up the choices from a table (choices)
I want to limit the secondary choice lookup to EXCLUDE the item chosen in
the Primary choice

Can I create a query as my source for secondary choice that excludes primary
choice? (a not in, query)?

Thanks
 
SELECT FIeld1, Field2 FROM MyTable WHERE Field1 <> Forms!MyForm!MyListbox

If your listbox allows multiselect, you'll need to loop through the
ItemsSelected collection and build a string, then dynamically change the SQL
associated with the second list box.
http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" is an
example of what I mean (except for the dynamically changing the SQL part...)
 
PrimaryChoice and SecondaryChoice are fields in the same record?

If the goal is to prevent the student from making the same choice in both,
you could do that by opening your table in design view, and opening the
Properties box (View menu.) Beside the table's Validation Rule (in the
Properties box, not the lower pane of table design, which is the field's
Validation Rule), enter:
[PrimaryChoice] <> [SecondaryChoice]

You can do it by programmatically changing RowSource of the other combo in
the combo's AfterUpdate event, but there are several issues to solve to get
that working, e.g.:
- display problems with other rows of a continuous form if the combo's bound
column is zero-width;
- what to do if the SecondaryChoice already contains the same value when
PrimaryChoice is changed;
- need to call the same code in the Current event of the form, so the combo
is updated as you move record;
- need to call the code in the Undo event of the form to reset the combos,
but in this case based on the OldValue of the combos instead of their value.
 
Followup:

To address your specific question, there is an example of how to change the
RowSource of one combo from another in this link:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

And the SQL statement might be something like:
"SELECT Field1 FROM MyLookupTable WHERE Field1 <> " & Me.[PrimaryChoice]
& " ORDER BY Field1;"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen Browne said:
PrimaryChoice and SecondaryChoice are fields in the same record?

If the goal is to prevent the student from making the same choice in both,
you could do that by opening your table in design view, and opening the
Properties box (View menu.) Beside the table's Validation Rule (in the
Properties box, not the lower pane of table design, which is the field's
Validation Rule), enter:
[PrimaryChoice] <> [SecondaryChoice]

You can do it by programmatically changing RowSource of the other combo
in the combo's AfterUpdate event, but there are several issues to solve to
get that working, e.g.:
- display problems with other rows of a continuous form if the combo's
bound column is zero-width;
- what to do if the SecondaryChoice already contains the same value when
PrimaryChoice is changed;
- need to call the same code in the Current event of the form, so the
combo is updated as you move record;
- need to call the code in the Undo event of the form to reset the combos,
but in this case based on the OldValue of the combos instead of their
value.

R Fourt said:
access 2003
Each person's record has a primary and secondary choice of assignments

Primary choice field looks up the choices from a table (choices)
I want to limit the secondary choice lookup to EXCLUDE the item chosen
in the Primary choice

Can I create a query as my source for secondary choice that excludes
primary choice? (a not in, query)?
Click to expand...
Click to expand...
 
Back
Top