It's been a very long time since I've built an MSAccess application, but I am building a customer quoting application for a friend, where a salesperson can select a customer on a main form, and select multiple items on a subform. Each line item in the subform can have multiple options associated with it. For example, Customer A is being quoted for multiple vehicles. Each vehicle is a line item in the subform, and each vehicle has multiple options associated with it. The options for each vehicle can be broken down into 2 categories: "must quote" and "customer options". The "must quote" options differ from vehicle to vehicle, as do the "customer options".
My design approach has been to create button for each line item in the subform, that opens up a separate window that shows both groups of options in their own list boxes, so that the sales rep can see what "must quote" options are being included (but cannot alter them in any way, shape, or form). In opening this separate window, I am passing the QuoteID, LineItemID, and ProductID via OpenArgs, and storing these values in hidden labels, for use in the separate queries for each listbox. This pop-up window has no Record Source of its own, however the listboxes have their record sources set to dynamic SQL pieced together at Form_Load(), using the OpenArgs values sent over.
My goal is to have the sales rep select the "cutomer options", and when clicking the Save and Close button on the options window, all options (both "must quote" and "customer options") are saved to the tblLineItemOptions table. I know I also need the ability to go back to any given line item, and modify the selected "customer options" at any time before the quote is finalized, as well as deleting any options if the vehicle changes in the main quote subform (sounds like a Cascade Delete option on the table join to me?). I also need the abilty to NOT save any quote options when the user clicks the Cancel button on the options window. It is also possible that no "cutomer options" are selected, however the "must quote" options, by their nature, must be included for the selected vehicle.
I thought about creating a recordset in VBA code for the options form, and INSERTing each record on Save and Close, but that didn't seem to me to cover the "if there are changes to the customer options" scenario. I've spent a couple of days (and sleepless nights) thinking about how to do this correctly, to the point where I'm positive I'm over-thinking this.
As always, any guidance and assistance is greatly appreciated
Here is a simplified table relationship for the quote portion of the application:
tblQuotes ---(1-N)--->tblQuoteItems ---(1-N)--->tblQuoteItems_Options