Populate fields in a table using fields from another table

[MattFitzgerald]

My Forms & Tables:-
Main form is Frm_LE_List (contains Customer Details) Stored in Tbl_LE_List
Which contains subform Frm_VOL_References (Contains Orders known as VOL's) Stored in Tbl_VOL_References
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_Lookup (This contains information used to populate Tbl_Order_lines

Relationships:-
Tbl_LE_List is related to Tbl_VOL_References by field [LE ID] (one to many)
Tbl_VOL_References 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_Lookup (Here in lies my question)

Question:-
Tbl_Rate_Card_Lookup is a table provided to me by an external source as an when changes are made to it.
The Tbl_Rate_Card_Lookup has the product information to populate most of my fields in my subform Frm_Order_Lines.
Both Tbl_Rate_Card_Lookup and Frm_Order_Lines have the following fields in common:-

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)

As you can see above the Product description should always stay constant though new product may be added and old ones removed.
But month on month the other fields relating to the product may change so no point linking Tbl_Order_Lines to Tbl_Rate_Card_Lookup 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_References which allows me to select a product from Tbl_Rate_Card_Lookup 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.

[MattFitzgerald]

I have almost resolved this now

I created a form based on the rate table and when I click add record this opens.

I then added a combo box and using the combo box wizard selected the option allowing your selection in the combo box to select the record set.

I added a button to this table saying proceed which opens a further form which is based on the table Tbl_Order_Lines and the default properties look at the field based on my selection on the previous table. I then only need to fill in the field not populated.

Then I click on a button Save and Close

Which saves the line item then closes the form.

All I need now is the code to attach to the Save and Close Button which will refresh my sub form Frm_Order_Lines

Any help is much appreciated.

Best Regards Matt Fitzgerald

[MattFitzgerald]

Am adding updated zip so can see where upto so far any other suggested improvements are welcome as I may not be structuring things in most efficient way and any advise is much appreciated. Especially advice on best ways to refresh forms after making additions e.g adding new LE then being able to select it.

[MattFitzgerald]

I think part of my problem is I do not fully understand the difference between

.Recalc
.Refresh
.Requery

as I think in some of the problems I have been having is if I use .recalc on a subform it seems to update the main form details as well and I should have used refresh.

can anyone explain the differences between these 3?
And any pitfuls to watch out for as I think in my learning I am falling into all of them!

Best Regards

Matt Fitzgerald