Changing the combo box based on the entries in previous records

[magicdds-]

I have a form with a subform. The subform has a combo box that lists people
that might share in paying for a product (The list of people is in a table).

The user, when in record #1 of the subform, selects a payer from the combo
box and in the next field, types in how much that payer will pay towards the
product (In the main form). Then the user goes to record #2 and clicks a
payer in the combo box. The problem is the payer from record #1 is still
listed in the combo box, but that payer should not have 2 records created for
the same product.

Is there a way to have the combo box list only those payers that have not
already had a record created for this product, so multiple records cannot be
created for the same payer, for a given product?

Thanks
Mark

 

[Jeanette Cunningham]

Mark,

The problem is that on a continuous Form or subform, there is really only
*one* combo box - displayed many times. If you change its properties (its
RowSource in this case), all rows reflect that change.

One somewhat snarky but effective work around is to put a Textbox onto the
form, carefully superimposed over the text area of the combo box (don't
cover the dropdown tool though). Set its Control Source to either a DLookUp
expression looking up the current record's value from the lookup table, or
(if practical) base the Form on a query joined to the lookup table and
simply make it a bound textbox.

Set the textbox's Enabled = No, Locked = Yes, Tab Stop = No so the user
can't do anything with it; it's for display only. You may need to move it in
front of the combo box (with Move To Front on the Format menu).

When the user selects the dropdown, the combo box data will come in front
and allow (filtered) selection; when it's not selected, the user will see
the textbox.


Jeanette Cunningham