Unbound Subform Control (1 Viewer)

[modest]

The best way to handle this is to show you what it looks like and go from there, so please see the attachment.

I have a subform used to track machine-part orders. The subform is bound to a table that has:
- Part Num (PK/FK)
- PO Num (PK/FK)
- Paid Price
- QTY

The Part Num field is a combo (drop down) box. It's rowsource selects the PartID and PartName from a Part table. The Part table looks something like:
- Part Num (PK)
- Supplier ID (PK/FK)
- Description
- Base Price



This all worked perfect, but now I wanted to help the user out a little bit by providing another field (Supplier) to the Subform so that the user could only select part numbers for that supplier.

I did this adding an unbound combo box that has a rowsource based off a different table (Supplier). It selects the Supplier ID. The new rowsource for Part Num looked like:
SELECT Parts.[Part Num], Parts.Description
FROM Parts
WHERE (Parts.[Supplier ID]=forms!main!po!subOrders!cboSupplier);


To explain the WHERE, Main is my form, PO is a tab on the form, subOrders is the subquery, and cboSupplier is the unbound combo box on the subquery.

My problem is that when I want to add a new part to the order, if I choose a new supplier it will change all the other suppliers above it. Going back to the picture; if I choose MIJACK they all become MIJACKs, if I choose OTHER they're all OTHER.

This has no affect on the Part Number, so I'm not worried data-wise, but the users are confused. Is there anyway (without adding a field to a table) to keep the supplier static for each row?

Thank you for your help.