[Solved] Subform From Query Not Linking with Mainform Record

[desagas]

Hello, I am so happy I found a place to ask my question:

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"

Again, this code works fine.

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.

[Villeroy]

Turn it from direct SQL to parsed SQL. If this is not possible, create another parsed query like

Code: Select all

SELECT * FROM "Direct_SQL_Query"

and use that for the form.

[desagas]

Thank you for your answer, but it is vague. There are over a dozen places I can add SQL code. Should I make a new query with what you said, or should I add that code to a form control, or a field control, or what do you mean? I will try to do it that first way I mentioned an see what happens.

[desagas]

So, it can not be the first way I mentioned.

Code: Select all

SELECT * FROM "RecipeNameBuilder"

Code: Select all

The SQL command leading to this error is:

SELECT * FROM "RecipeNameBuilder" WHERE ( "RecipeID" = :link_from_RecipeID )

But, perhaps I do not understand what you were suggesting.

[Villeroy]

One-to-many and many-to-many relations in tables, queries and forms with subforms and lisboxes.: download/file.php?id=11250

[desagas]

This kind of helped, but was extremely obscure: "here's a base that works, but, not exactly, learn your own way from it ..."

The only thing related out of it was this: setting the master form data to my recipes table, then the subform data to my many to many table I have (recipe outputs), and then setting the name listbox data to RecipeID and its value to Query -> NameBuildingQuery. From here, I just manipulated enabled to false, read only to true, and set rows to 1. So, while it still has a row in side of it for every single recipe output in the linked recipe, they are all identical (6 recipe outputs, means the listbox shows 6 linked query titles, all identical).