I have what seems a simple problem and I feel that there must be a straightforward solution:
I have a form of Fabrication Orders. Then I have a subform of assigned Sales Orders. The Sales Orders can only be assigned once and to one Fabrication order. I made something like a junction table that matches each Sales Order with a Fab(rication) Order. In this table the Sales Order is indexed as a unique value.
So in the form for Fabrication Orders I have a subform based on that junction table where the user can select/assign the Sales Orders. I am trying to set the first field as a combo box that selects the Sales order from a recordsource query that only pulls "unassigned" or "available" Sales Orders, i.e, Sales Orders that aren't already assigned to previous Fab Orders.
But when I did this the previous Fab Orders now have a blank space where I know there were selected Sales Orders. It's like the database is filtering what Sales Orders I can see for all records not just the new records.
Is there a way to get the combo box to show the previously selected Sales Orders and only allow the user to edit or add in new Sales Orders that are not currently assigned?