I have built a main form to manage contracts, with several subforms to capture details like the associated products and cost allocations. The form works great (well, there's a couple of small UI things I don't like but I've decided to live with them). Functionally, it does everything I need.
Now I am building a separate main form to manage requisitions (one-time purchases vs contracts). The primary difference between the two (from the underlying field's perspective) is a checkbox; contract or not? However, from a UI standpoint, they are very different. The contract # on the contract form would be the requisition # on the requisition form. Some fields would not be displayed at all (like termination notice date) and some fields would be defaulted (contract box unchecked, intend to renew unchecked, etc.).
I want to basically copy the contract form in its entirety, modify the main form and reuse the subforms as they are. Here's the rub: the subforms' underlying queries filter based on a control on the main form. How do I modify a query to reference a control on Me.Parent as opposed to [Forms]![MainForm]![ControlName]? Changing the query in SQL view was not successful, so I'm guessing I need to build some sort of VBA routine to either use the Me.Parent construct or to wholesale change the RecordSource of the subform based on the parent form it's currently displayed on.
Any ideas?
Thanks,
Phil