Solved Cannot add records to subform.

[ClaraBarton]

This is a subform that goes on a recipe form (RecipeID to RecipeIDFK). Everything worked fine until I added the qryMeasure to the subform. Now the subform is not updatable although the query itself is updatable. The new records on the subform show error in the RecipeIDFK. I considered using a subform for the Measure on the ingredient subform but that doesn't work. Am I overlooking something obvious?

 

[theDBguy]

Sometimes, using an OUTER JOIN could render the query result as read only.

 

[MajP]

Are you using a lookup table for ingredientidfk?

 

[ClaraBarton]

lookup table for the unitIDFK

 

[ClaraBarton]

This is a left join and I want it editable

 

[GPGeorge]

This is a left join and I want it editable

Unfortunately, though, it may not be editable. You need to consider alternatives.

I'm sort of guessing, though, that you may not need the unitID in the table bound to the subform anyway.

We really need to see the tables involved and possibly even a sample accdb to anlyze.

 

[Mike Krailo]

Why not create one query based on all the tables instead of a table to a query?

 

[Mike Krailo]

This is a subform that goes on a recipe form (RecipeID to RecipeIDFK). Everything worked fine until I added the qryMeasure to the subform. Now the subform is not updatable although the query itself is updatable. The new records on the subform show error in the RecipeIDFK. I considered using a subform for the Measure on the ingredient subform but that doesn't work. Am I overlooking something obvious?

View attachment 110394

After looking at this more closely, you should post your table relationships. The units of measure should be in the joining table for recipe to ingredients. You currently have the units in a query which doesn't make any sense to me as it has both UnitIDFK and Unit. It should simply be the join table which acts as a steps to build the recipe as well. See following diagram for example.

 

[cheekybuddha]

Please post the SQL of qryMeasure

 

[ClaraBarton]

qryMeasure
SELECT tblIngredientUnit.UnitIDFK, tblIngredientUnit.QuantityText, tblUnits.Unit, [QuantityText] & " " & [Unit] AS Measure, tblIngredientUnit.IngredientIDFK
FROM tblUnits
RIGHT JOIN tblIngredientUnit ON tblUnits.UnitID = tblIngredientUnit.UnitIDFK;

qryIngredient
SELECT t_ingredient.IngredientID, t_ingredient.RecipeIDFK, t_ingredient.ingredienttext, t_ingredient.USDAIDFK, t_ingredient.ingredientindex, t_ingredient.heading, qryMeasure.QuantityText, qryMeasure.Unit, qryMeasure.Measure, qryMeasure.IngredientIDFK, qryMeasure.UnitIDFK
FROM t_ingredient
LEFT JOIN qryMeasure ON t_ingredient.IngredientID = qryMeasure.IngredientIDFK
ORDER BY t_ingredient.ingredientindex;

 

[cheekybuddha]

Try adding tblIngredientUnit.tblIUID to qryMeasure.

Sometimes you need the PK to make the query updateable

 

[ClaraBarton]

The query was always updatable. It just didn't work in the subform. I went back and redid the tables. I decided it wasn't a true many to many so I added the tblIngredientUnit information to the t_ingredient table. It's now working and I thank you for your time.

 

[ClaraBarton]

And just to confess my sins... I discovered that the UnitIDFK on t_ingredients was a short text type instead of a number. Never would have worked!