Four Tables:
Vendor (Vendor details for contracts)
Product (Product details, with VendorID to tie them to a vendor)
Order (Contract and One-Time Order details, also with a VendorID but this time for the reseller of products)
OrderProduct (join of Order and Product, to record multiple products against one contract)
1) I am building a form for entering and editing contracts, with a subform for adding all the products tied to the contract (continuous form). The challenge is in the filtering of the Product combobox. Rowsource = Product where Product.VendorID = Form.VendorID. If adding a new record, I'd want the user to select the Vendor via an unbound combobox to filter the Product combo box. That's straightforward and I have that working.
However, if editing an existing record in the same form, the unbound Vendor combobox is unpopulated, which means the Product combobox doesn't have a value to use as criteria so it is also blank.
2) I thought of adding the VendorID to the OrderProduct table to make it a little easier, so I added a bound Vendor combobox next to the Product combobox. Works like a champ to bring up the existing record. But if someone chooses to add a different Vendor to the same contract, the Product RowSource changes and all the comboboxes above the record go blank (because the control is looking at the new record's Vendor combo box. So now I've created an issue with NEW records...
If you need to see the database, I'd prefer to share it privately...it's for the company I work for and prefer not to have it freely available.
Thanks in advance,
Phil