I am working on a recipe database, that can iteratively calculate necessary ingredients to make said thing, but that is not related here.
I have base linked to mysql, and am, thus, using syntax related to mysql, and can not upload my database here.
I have a query created that looks like this, which works perfectly, which automatically generates a recipe name for me, that says ABC is made using machine X:
Code: Select all
SELECT "recipes"."RecipeID",
concat(group_concat("ingredients"."IngredientName" SEPARATOR ', '), ', in the ', "machines"."MachineName") AS "RecipeName"
FROM "recipes", "machines",
"recipe_outputs", "ingredients"
WHERE "recipes"."MachineID" = "machines"."MachineID" AND
"recipe_outputs"."IngredientID" = "ingredients"."IngredientID" AND
"recipe_outputs"."RecipeID" = "recipes"."RecipeID"
GROUP BY "recipes"."RecipeID"
My table for this links a single RecipeID field in a recipes table and relates it to many rows with a RecipeID field in a recipe_outputs table. So, say, recipe 123 creates 10 of output A and 20 of output B. My query above joins all the outputs and the machine used. So, 'A, B in the Oven,' for example. It then groups by RecipeID to make sure I get a unique name for all recipes. This works fine.
Let's switch to my form. my main form is based on the 'recipes' table, and is displayed as a table. Following that, I have sub forms for 'recipe_inputs' and 'recipe_outputs' linked to the main form, which work perfectly. Here is my problem: I created a subform, add a text box, link the main form to the sub for, with RecipeID master linked to RecipeID slave, exactly the same as my 'working perfectly' other subforms, and set the control data to 'RecipeName,' and I do see RecipeName text appear when I open the form, but, it does not change as I navigate the records. So, while they are linked, and it loads them, it does not update the textbox with the selected record, it remains always the same: the first record.
I tried to prove that it is loading them all, and it is. I changed the text box to a listbox, and all recipe names are in the box, but this subform is not being adjusted by the selected main form record.
I looked over all the examples in this forum, and could not find my fix. I either need to dynamically set the filter of the subform to the record in question, or have the text field in the subform use a query of its own, say as a list box, which sets criteria to RecipeID. But, the dozen ways I have tried to do so have not worked.
Any help would greatly be appreciated.