Overview: I am building an IT financial management database. The database has a table/form for storing contract details, and a separate table/subform for storing the products associated with each contract. That portion of the database is working very well. The database also has allocation tables to store cost allocation data for each of the products in that contract against various things like the IT service catalog, business unit, etc. The allocation table includes a percentage field so that one can allocate, say, 30% of a product to Business Unit A, and 70% of the same product to Business Unit B. The allocation tables are many-to-many tables, joining IT service catalog items/business units/etc to products. Building the form for this data entry is where I need help.
Details: The contract table has a PK called OrderID. Each product associated with that contract has the respective OrderID as FK, but also a OrderProductID as PK. Each allocation table (let's just use the Business Unit allocation table) has a PK of OrderBusinessUnitID, with FKs of OrderProductID and BusinessUnitID (drawn from the business unit tables) with a field to store the percentage of allocation. Currently, I have a form whereby the user enters the contract details, with the subform where they select the products on the contract and enter quantity/cost/accounting info. The subform is a continuous form to make the product data entry fast, and to make data carryover a little simpler. However, there's just not enough screen real estate to also have the user do the allocations at the same time as the product entry (which would be ideal).
Courses of Action considered:
1) Create an allocation button on the contract form that opens another form to do allocation for all the products associated with that contract. I like this idea the best, and if each product was 100% allocated to a business unit it would work perfectly. Just open the form with a list of all the products in a continuous form, then have combo boxes to select the allocations. But the form needs to allow for multiple allocations per product, and I don't know how to do that.
2) Create an allocation button on the products subform for each product that opens another form to do the allocation. This is probably the most easily workable solution, but it means the user has to click a button after each product to enter the data for that product. There's going to be lots of carryover/repetitive data, and bouncing between forms product by product (some contracts have fifty or more products) is not ideal. This is my least favorite option.
3) Add the allocation combo boxes to the existing product subform (continuous form). This might be ok, but 1) it consumes a lot of vertical screen real estate and 2) I've rarely had any success with a single form drawing on two tables (relationship or not). If I can work out the screen real estate piece, I'd probably be ok with this one.
Left field thought: Is it possible to use a split form as a subform? It would have to open up in a separate popup window, but it would probably resolve the screen real estate issue and allow me to have all the data entered in one form (as long as I can draw on two tables...)
So I'm throwing this out to the hive mind. Does anyone have any suggestions on the best way to handle this kind of thing? One caveat: I'm an IT executive. I am not experienced with VBA and don't really have the time to learn/maintain the skill set. I'm writing this database because 1) I need it to help defend my budget and 2) because my data analysts are too busy doing other things. I can follow instructions, and understand a little VBA, but I'm never going to write long subroutines...