Help with using subform data in Master-form fields

[TheWiseGuy]

OVERVIEW:
We have a PRODUCTS sub-form included in a CONTRACTS Main form that displays a single matching PRODUCTS record based on [productIdCode]. When a product is selected in [ProductsIdCode] (ComboBox) a corresponding record from the PRODUCTS table is displayed. Only one product is allowed per contract. The purpose of the PRODUCTS sub-form is to show (non-editable) product information and “suggested pricing”. Actual pricing is included in the CONTRACT records and typically varies from the “suggested” information.


OBJECTIVE:
To use (copy) information from the sub-form fields to similar fields in the CONTRACTS main-form as “default” entries using a control button. The “copied” values must be editable (not linked to the PRODUCTS record).


EXAMPLE:
The PRODUCTS table contains information on generic equipment including “product id code”, “product description”, “suggested price” and “suggested down payment”. A single product record is displayed in a sub-form based on a field value entered on a “new CONTRACTS” form. The “new CONTRACTS” form also contains separate (non-linked) fields for “product description”, “suggested price” and “suggested down payment”. We need to be able to “copy” the information from the PRODUCTS sub-form fields to similar fields in the main form (“new CONTRACTS”) using a “control button” and without affecting the information in the PRODUCTS table. The USER need to be able to “manually adjust” (override) the “product description”, “suggested price” and “suggested down payment” information in the “new CONTRACTS form on a case by case basis.

 

[JerryDennison]

I would link the ProductID to the products table. Then in each of the other fields I would set their default value to a DLookUp() function that returns these from the Product table. When the record is saved these values (or whatever they are changed to) will become those saved in the record.

So, for example, for the product description you could use something like the following in the Default value property of the control:

=DLookUp("[Product Description]", "Product Table", "[ProductID] = " & Forms!FormName!ProductID)