Main form is Frm_LE_List (contains Customer Details) Stored in Tbl_LE_List
Which contains subform Frm_VOL_Referen ces (Contains Orders known as VOL's) Stored in Tbl_VOL_Referen ces
This subform has subform Frm_Order_Lines (Contains line items for orders) Stored in Tbl_Order_Lines
I also have a table Tbl_Rate_Card_L ookup (This contains information used to populate Tbl_Order_lines
Relationships:-
Tbl_LE_List is related to Tbl_VOL_Referen ces by field [LE ID] (one to many)
Tbl_VOL_Referen ces is related to Tbl_Order_Lines by field [VOL Ref] (one to many)
Currently ther is no relationship between Tbl_Order_Lines and Tbl_Rate_Card_L ookup (Here in lies my question)
Question:-
Tbl_Rate_Card_L ookup is a table provided to me by an external source as an when changes are made to it.
The Tbl_Rate_Card_L ookup has the product information to populate most of my fields in my subform Frm_Order_Lines .
Both Tbl_Rate_Card_L ookup and Frm_Order_Lines have the following fields in common:-
Expand|Select|Wrap|Line Numbers
- Product Description,Text,PK (No Duplicates)(Products may be added removed each month)
- Product Group 1,Text (Could change)
- Product Group 2,Text (Could change)
- Sch 5 Target Area,Text (Could change)
- LB Commission Unit,Number,Currency (Could change)
- LB Commission Percent,Number,Percentage (Could change)
- Cobra Statement Area,Text (Could change)
- LB Payplan Area,Text (Could change)
- % payable to agent,Number,Percentage (Could change)
But month on month the other fields relating to the product may change so no point linking Tbl_Order_Lines to Tbl_Rate_Card_L ookup as so many possible changes.
What I would like to do is when an order is placed have an "Add New Order Line" button in my form Frm_VOL_Referen ces which allows me to select a product from Tbl_Rate_Card_L ookup and the fields from the product selected used to populate the new record in Frm_Order_Lines
Half the battle is knowing what you want to do, I need help with the other half as I have no idea how to do it. Please be kind and gentle with any explinations. I am going to try and attach a zip file with my design so far as it may help fill in any gaps in my explanations.