Not updateable subform

[Tep]

Hi,

I have a form with a unbound combo box (Natcd) and a subform (Missing Proportion% subform).
The form and subform are linked with Natcd (master) and [Ingredient name] (child).
The record source of the subform is table Foodcomposition.
The subform is a continuous form. The fields on the subform should be updateable.
Field Ingredient on the subform gives me some troubles.
This field stores a code of which the description can be found in another table 'Food'.
I would like to insert the description of Ingredient to the subform,
but this means that the record source of the subform will be from two tables and not updateable any more.

So: has anybody any idea how to insert a field (this one should not be updateable) from table food in an updateable and continuous subform with record source table Foodcomposition?
Food and Foodcomposition can be linked by fields Ingredient.

This is the record source for the subform which is not updateable:

SELECT Foodcomposition.Food, Foodcomposition.[Ingredient name], Foodcomposition.Ingredient, Food.Foodname
FROM Foodcomposition INNER JOIN Food ON Foodcomposition.Ingredient = Food.Ingredient
ORDER BY Foodcomposition.Food, Foodcomposition.Ingredient;

I hope someone can help me with this!
Thank you in advance,
Tep

 

[CJ_London]

There are a couple of possibilities depending on what you are trying to do

You can link the two tables but in the form data recordset type change from dynaset to Dynaset (Inconsistent Updates). But make sure the field on the form that selects the ingredient is picking up the code from food.

A better way is probably to do the following. Don't have the food table in your form recordsource but make the form control for incredient a combo box which has a rowsource of the food table (typically ID and ingredientname), set the bound column to 1 and the column widths to 0,2.

You might want to consider using your subform in datasheet view - it gives the flexibility of among other things being able to adjust column widths which can be useful for long names