Deleting Items from a combo box once selected

[Chezza]

Hi All,

Thanks to those that have helped me before.

It’s been a couple of weeks since I posted (I can only play with this during my spare time at work) so here’s the set up again- we receive many “little” shipments of four different versions of something. We combine these into one “big” shipment which we send out to be processed.

Currently I have one main form for the big shipments, based off of a “big shipment” table and a subform, based off of a query. In the subform (a continuous form), the user selects the little shipments to be included in the big shipment from a combo box (based of a query). The quantity of each version, for each little shipment, auto-fill and I get a grand total for the big shipment.

I want the user to only be able to select little shipments that have not been included in a big shipment from the combo box. There is a yes/no field in the little shipments table and the combo box is filtered to let only those marked “no” show up. When a user selects a little shipment, then the Sent field is automatically updated to Yes.

Everything works except when I close the main form and open it again, all the entries in the combo boxes have disappeared. Which, now that I think about it, makes sense. If the combo box is filtered, then the ones marked “no” will no longer appear in the query, so they aren’t going to appear in the combo box.

However, I am not sure how to get around this. Can someone point me in the right direction? Is there another way of doing the same thing?

Thanks
Chez

[NeoPa]

It seems to me you're asking it to do two different (mutually incompatible) things on the same form.
If the form needs to show existing records which have "little shipments" already selected and assigned, you simply cannot restrict the ComboBox to those items that haven't been selected yet.
You could try a check in the AfterUpdate procedure for the ComboBox that cancels the update if you try to add an already used item.