I have a very specific form that I'm trying to build and am having difficulty with it.
Table Design:
-tblEstimates contains the estimate number (primary key), customer (there is a proper relationship established and a seperate Customer table), and date estimated.
-tblServices ID (primary key), contains estimate number (foreign key to tblEstimates), service (coming from a seperate lookup table containing all the services)
-tblItems ID (primary key), contains service number (foreign key to tblServices), item, quantity, item cost
I'm building an estimate form, and there are three hierarchy levels to the form: estimate number, service, and item. Each estimate can have many services, and each service can have many items. I have created a form containing a subform, which contains a subform. The form is the estimate level, the subform is service level, and the subform inserted in the subform is the item level. The items subform is a continuous form. So basically with this current form I can have one estimate, with one service, with 1-many items. What I need is a form that can have one estimate, 1-many servivces, and 1-many items. (Each estimate can have 1 service or many services).
I've been working on this for a week and have been able accomplish this if the subform (with items subform inserted) is in datasheet view, but I don't like that as this form will be used by other users, and it isn't intuitive and smooth to navigate in my opinion. I've also been able to insert a continuous form for items into the footer section for a continuous form for services, but this too is not intuitive as the items for service "x" are not listed directly below service "x", but rather in the footer of the form.
What I'm trying to accomplish is to build take the form I currently have, and insert a button that adds another subform to the main form. This way there is no unnecessary clutter in the form (I don't want to create a form with 20 subforms if only one subform is required at times). It would also be intuitive, as the user can select a service, then select multiple items directly below it, and then repeat the process by clicking the add service button if needed.
Does anyone know if this is possible through VBA? If so, can you point me in the right direction?
Thanks in advance.