Hi.

Before asking the question let me tell you about my case... (i indented it in case you can undestand the question without the long explanation)

I have already set my tables and their relationships, part of the structure is:
tblProducts : ID - ProductCode - ProductDescription - UnitsToProduce (note the product will be produced only once, that's why no extra table requiered)
tblMaterials : ID - MaterialCode -MaterialDescription
tblProductsMaterials: ID - ProductID - MaterialID - QuantityPerUnit

Now I have a query:
qryMaterialRequirements: MaterialCode - MaterialDescription - ProductCode - ProductDescription - UnitsToProduce - QuantityPerUnit - Total

With this I make a form that uses a that query but totalized by Material, so I have:
frmMaterials: MaterialCode - MaterialDescription - GrandTotal

But since the detail of this total is useful, I use a subform based also on the query, but not totalized:
sfrmMaterialDetails: ProductCode - ProductDescription - UnitsToProduce - QuantityPerUnit - Total

This layout work great... but this form must allow for the user to make editions in the subform. The problem is that if the user edits the ProductCode, it doesn't change the product that consumes this material, it changes the product code!
I do get why, and I think what should be there for edition is the foreing key (the one in the many side, it's called foreing, right?)... but this key is a meaningless number!!

So which is the way to go? Is my layour wrong? Lookup field in the form? Another?

Thanks a lot!!

I'm not sure if I follow you but I think your form should be based on a query of the products-materials table only and you should use comboboxes to select the products and the materials and put those id fields in the products-materials table.

The form is not to entry which product needs which material. It's for after I have the tables filled to analyze the data. So note the main form is based on a totals query. And both the form and the subform need at the very least the UnitsToProduce from Products and QuantityPerUnit from tblProductsMaterials.

Apart from that I need to displaye the info (code and description) for both the products and the materials... my question was how to SHOW that info, but that if the user edits it, he would be editing the product that uses that material, not the actual product code/description.

Thanks.

Put a description field in tblProductsMaterials.

What are the LinkMasterFields and LinkChildFields of the SubFormControl?

It's ID from materials in both (the field is in the query the forms use as source). Since I want the subform to list all the products-materials in which the material is the selected material in the main form.

Set the Locked Property of the Controls bound to ProductCode and ProductDescription in the SubForm to False then the user won't be able to edit them.

You may want to check Access Help on both the Locked Property and the Enabled Property also.

Thanks. But I do want the user to edit those... only that I don't want what they edit to be the code or description in the products table, but the ProductID field in tblProductsMaterials, changing which product actually uses that material.

It's like I want to DISPLAY the product code and description, but actually have behind the ProductID field in tblProductsMaterials... like in a lookup field in a table I think (only I'm told those are bad practice?).

It sounds to me that you are using an incorrect RecordSource for the Subform, possibly caused by the unconventional use of [tblProducts], i.e. "note the product will be produced only once, that's why no extra table requiered".

Please post the SQL String of the Query being used as the RecordSource for the SubForm.

SELECT qryMaterialRequirements.ID, qryMaterialRequirements.ProductCode, qryMaterialRequirements.ProductDescription, qryMaterialRequirements.UnitsToProduce, qryMaterialRequirements.QuantityPerUnit, qryMaterialRequirements.Total
FROM qryMaterialRequirements;

This is it. The ID is from the Materiales table, to filter according the selected material on the main form.
Just in case I repeat that the filter in the subform IS working fine, the result i SEE in the subform is 100% the desired... just that when I edit the qryMaterialRequirements.ProductDescription for example, I'm editing the tblProducts.ProductDescription... I of course do not want that! I want to change the product that is using this material (tblProductsMaterials.ProductID) without showing this ugly, meaningless number.

Since you use a Query as the data source, please post the SQL String of the Query [qryMaterialRequirements] also.

Sure, here it is:

SELECT tblMaterials.MaterialCode, tblMaterials.MaterialDescription, tblProducts.ProductCode, tblProducts.ProductDescription, tblProducts.UnitsToProduce, tblProductsMaterials.QuantityPerUnit, Round(tblProducts.UnitsToProduce*tblProductsMaterials.QuantityPerUnit,0) AS Total
FROM tblProducts INNER JOIN (tblMaterials INNER JOIN tblProductsMaterials ON tblMaterials.MaterialCode = tblProductsMaterials.MaterialCode) ON tblProducts.ProductCode = tblProductsMaterials.ProductCode;

Did you copy the SQLs from your database and paste in your post or did you re-type the SQLs?

The SQLs posted are not consistent... The second stage SQL (which you posted first and is used as the RecordSouce for the SubForm) has the selection:

qryMaterialRequirements.ID

but the first stage SQL (i.e. the SQL for qryMaterialRequirements) does not have any Field named ID in the selection list???

Sorry... that's because (1) I've made some changes since posting the original question (2) the situation is actually a bit more complex, I abstracted from it only as much as needed my question and (3) the original names are in spanish so each time I post here I translate all the names.

OK... Please post the 2 SQLs again and make sure that they are consistent for us...

Sorry for the trouble... I hope this is OK.

Query

SELECT tblMaterials.MaterialCode, tblMaterials.MaterialDescription, tblProducts.ProductCode, tblProducts.ProductDescription, tblProducts.UnitsToProduce, tblProductsMaterials.QuantityPerUnit, Round(tblProducts.UnitsToProduce*tblProductsMaterials.QuantityPerUnit,0) AS Total
FROM tblProducts INNER JOIN (tblMaterials INNER JOIN tblProductsMaterials ON tblMaterials.MaterialCode = tblProductsMaterials.MaterialCode) ON tblProducts.ProductCode = tblProductsMaterials.ProductCode;


Subform Recordsource

SELECT qryMaterialRequirements.MaterialCode, qryMaterialRequirements.ProductCode, qryMaterialRequirements.ProductDescription, qryMaterialRequirements.UnitsToProduce, qryMaterialRequirements.QuantityPerUnit, qryMaterialRequirements.Total
FROM qryMaterialRequirements;

So the main form lists the materials, totalizing the requirements, and the MaterialCode from the selected record is linked to the qryMaterialRequirements.MaterialCode of the subform, which shows the details for that material (the products that use it and how much they consume).

What you posted is still not consistent with the description provided so far...

Earlier, you wrote that the LinkMasterFields / LinkChildFields is the ID of Material but I cannot see any ID in the SQL used as the RecordSource for the SubForm.

I suspect that to prevent the user from actually modifying the ProductCode/ProductDescription (from tblProducts), you need to include the ProductID from tblProductsMaterials in the selection list and not the ProductCode / ProductDescription from tblProducts. You then use a ComboBox bound to the Field ProductID to show the ProductCode. The RowSource of the ComboBox should be base on tblProduct and included the Fields ID, ProductCode, Product Description with the ID Column as the BoundColumn. You can then use a Calculated TextBox Control next to the ComboBox with ControlSource

=ComboBoxName.Column(2)

to show the ProductDescription corresponding to the ProductCode shown in the ComboBox.

This way, when the user select a different ProductCode in the ComboBox, the user actually changes the ProductID in the tblProductsMaterials which is what you wanted.

I am not sure allowing the edit is the right process though since your Form is "Material" centric and the user only sees one Material component (out of many possible Material components that make up the Product) so how would the user knows the "total" picture of the Product he/she modifies...

Thanks for the answer!

I know it sounds weird to edit a product in that view, but in this specific case it's usefull.

You're welcome... Glad we could help...