How to make editable multi-column subforms based on many-to-many relationships? (1 Viewer)

CFMayr

New member
Hello.
I have a DB with this basic structure (the DB is more complex, but for the sake of this question that's not important):
  • Table Recipes: RecipeID, RecipeName, etc.
  • Table Ingredients: IngredientID, IngredientName
  • Table CookingTools: ToolID, ToolName, ToolDescription
As every recipe can have many ingredients, and every ingredient can be used in many recipes, I have other tables linking them (the same applies for the cooking tools):
  • Table RelsRecipesIngredients: RecipeIDFK, IngredientIDFK
  • Table RelsRecipesCookingtools: RecipeIDFK, ToolIDFK
I have a main form (based on the Recipes table) and two subforms for Ingredients and CookingTools with a combo box in each one, with IngredientIDFK as Control Source in one and ToolIDFK in the other. As Row Source I have a query like this:
Code:
SELECT DISTINCT CookingTools.ToolID, CookingTools.ToolName FROM CookingTools ORDER BY CookingTools.ToolName;
Other properties are:
  • Number of columns: 2
  • Column width: 0cm;4cm
  • Bound column: 1
  • Limit to list: Yes
  • Allow value list edits: Yes
And my NotInList code is something like:
Code:
Private Sub ToolID_NotInList(NewData As String, Response As Integer)
On Error GoTo Combo_Tools_NotInList_Err
    Dim intAnswer As Integer
    Dim strSQL As String
    intAnswer = MsgBox("The cooking tool " & Chr(34) & NewData & _
        Chr(34) & " is not present in the list." & vbCrLf & _
        "Do you want to add it to the list?" _
        , vbQuestion + vbYesNo, "Organization")
    If intAnswer = vbYes Then
        strSQL = "INSERT INTO CookingTools([ToolName]) " & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        MsgBox "The new cooking tool was added to the list." _
            , vbInformation, "CONABIO, 2018"
        Response = acDataErrAdded
    Else
        MsgBox "Please, choose a cooking tool from the list." _
            , vbInformation, "Organization"
        Response = acDataErrContinue
    End If
Combo_etnias_NotInList_Exit:
    Exit Sub
Combo_etnias_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume Combo_ToolID_NotInList_Exit
End Sub
Until now, I only needed to add the tool names and the ingredient names, so this method worked just fine. But now I must to show also the ToolDescription field, so the users can view and edit it. I somehow accomplished this by adding a text box, where the Control Source is ToolDescription. The problem is that there can be tools with the same name, but different description, like Cooking pot | Used by the Maya people and Cooking pot | Used by the Nahua people, but if I add the first one, when I try to add the second one, the description showed is from the first, and if I edit it, then the first also changes (obviously, since they have the same ToolName). I tried to use another table only for tool descriptions, and link it to CookingTools (using a ToolDescrID_FK in this table), but then again I failed to make a working subform. I have more or less the same problem with the Ingredients table, but in this case I need to add measuring units, like Rice | 2 cups or Parsley | 5 leaves.

My question is, how can I make a subform were I can not only view, but also add tools with the same name but different description (or ingredients with the same name but different measuring units)? Can you give me some suggestions and tell me where I'm making mistakes, please?
 

gemma-the-husky

Super Moderator
Can you give an example of an tool with the same name, but different measuring unit. You need some way of distinguishing the tools, so your PK (or at least a unique index) needs to include BOTH the tool name AND the unit. Then your problem should go away.

Or change the name to make it unique.


It seems to me you have two different things.

Rice|2 Cups really devolves to

product Rice
measure Cup
Qty 2

So in your recipe you would need to include the product and the measure as unique


If you needed, say

Recipe
Butter
Grammes
250
for a cake mix AND

Icing
Butter
Grammes
50

for an icing, or a fill then you may need to add a further component of STAGE (the red items above) to distinguish between the cake batter, and the cake decoration. The idea is to get a unique identifier for every row of the table with no redundant fields in the combined key, and no duplicates.
 

gemma-the-husky

Super Moderator
Code:
Cooking pot | Used by the Maya people and Cooking pot | Used by the Nahua people
Does it really matter whose make of pot is being used? Offhand, I wouldn't have thought that the make of pot matters. To me, it's like distinguishing between a Le Creuset pot, and a Cookworks pot. Important if you are selling them, but not so much if you are just using them.

However, it's your database, and you know why it matters. Therefore, if it does matter, then you need to give these different descriptions, and add a unique index to the description field. I would also add an autonumber key to the table, and use THAT in the recipe instructions

Bear in mind that if you use multiple different pots, you may end up with multiple recipes for the same thing, just because one uses a MAYA pot, and the other a NAHUA pot. Maybe you can manage the ingredients separately from the tools, but it sounds tricky to map out. Maybe the tools themselves aren't even so important at all.

Anyway, given your original notes about the cooking pots, I would have these names.
PHP:
Item  Name                          Description
1       Cooking Pot Maya        …..
2       Cooking Pot Nahua      ……
Now, you might also want to distinguish between sizes, as I am sure they have multiple sizes of cooking pot. I know cake tins are different diameters/heights/shapes, for instance.

so either

PHP:
Item  Name                         Size    Description
1       Cooking Pot Maya        Small          …..
2       Cooking Pot Nahua      Small          ……
3       Cooking Pot Maya        Large          …..
4       Cooking Pot Nahua      Large          ……
or include the Small/Large identifier in the tool name - but either way you get 4 rows, not 2. Now you HAVE to consider both the name AND the size to get a unique value. In the recipe table, you want to refer to this pot. You can store the name AND the size in the recipe table, but it's more convenient to use an autonumber ITEM code, and use THAT in the recipe table. It takes up less space, it's easier for you as developer to manage numbers, and it's easier to change the names and descriptions, as they are used only in the TOOLS table.

All of this is part of the data analysis phase. You need to think carefully about the real world entities you are managing, and design a data structure that enables you to manage that data through your forms. There is often some refinement needed, as you are finding - where you find your original model is not sufficient, and you have to go back and rework some aspects. The earlier in the process you can nail down the data structure the better. It's much harder to get a 75% complete project, and then realise your data structure needs changing, as it gives you a lot more work.

At this point the fact that you want a combo box is immaterial. You need to get the data organised before you start adding forms.

One big decision is whether you need separate tables for tools and ingredients. If you can put everything in a single table, it might be easier to design the database. Just have a flag to distinguish a tool from an ingredient. It may work, but it may not, but it's worth considering.

I hope this helps.
Good luck.
 
Last edited:

CFMayr

New member
Can you give an example of an tool with the same name, but different measuring unit. You need some way of distinguishing the tools, so your PK (or at least a unique index) needs to include BOTH the tool name AND the unit. Then your problem should go away.

Or change the name to make it unique.


It seems to me you have two different things.

Rice|2 Cups really devolves to

product Rice
measure Cup
Qty 2

So in your recipe you would need to include the product and the measure as unique


If you needed, say

Recipe
Butter
Grammes
250
for a cake mix AND

Icing
Butter
Grammes
50

for an icing, or a fill then you may need to add a further component of STAGE (the red items above) to distinguish between the cake batter, and the cake decoration. The idea is to get a unique identifier for every row of the table with no redundant fields in the combined key, and no duplicates.
I see. I didn't have considered this approach. Actually, I didn't know that you can have a composite PK. I'll check if this can lead me to a solution. Thank you for your suggestions! :)
 

CFMayr

New member
Code:
Cooking pot | Used by the Maya people and Cooking pot | Used by the Nahua people
Does it really matter whose make of pot is being used? Offhand, I wouldn't have thought that the make of pot matters. To me, it's like distinguishing between a Le Creuset pot, and a Cookworks pot. Important if you are selling them, but not so much if you are just using them.
Yeah, the thing is that this DB is part of a project concerning traditional Mexican gastronomy, and sometimes there are differences in shape, colors and the material used to make the tools. Then, I need to record even those little details :(
Anyway, given your original notes about the cooking pots, I would have these names.
PHP:
Item  Name                          Description
1       Cooking Pot Maya        …..
2       Cooking Pot Nahua      ……
I thought something similar (give different and unique names), but unfortunately I need the names exactly as they appear in the books I'm using as the base for this project. Sadly, that means that, if I need to distinguish between them, I need to do that in a separate column.

Now, you might also want to distinguish between sizes, as I am sure they have multiple sizes of cooking pot. I know cake tins are different diameters/heights/shapes, for instance.

so either

PHP:
Item  Name                         Size    Description
1       Cooking Pot Maya        Small          …..
2       Cooking Pot Nahua      Small          ……
3       Cooking Pot Maya        Large          …..
4       Cooking Pot Nahua      Large          ……
or include the Small/Large identifier in the tool name - but either way you get 4 rows, not 2. Now you HAVE to consider both the name AND the size to get a unique value. In the recipe table, you want to refer to this pot. You can store the name AND the size in the recipe table, but it's more convenient to use an autonumber ITEM code, and use THAT in the recipe table. It takes up less space, it's easier for you as developer to manage numbers, and it's easier to change the names and descriptions, as they are used only in the TOOLS table.
You mean like a foreign key?

All of this is part of the data analysis phase. You need to think carefully about the real world entities you are managing, and design a data structure that enables you to manage that data through your forms. There is often some refinement needed, as you are finding - where you find your original model is not sufficient, and you have to go back and rework some aspects. The earlier in the process you can nail down the data structure the better. It's much harder to get a 75% complete project, and then realise your data structure needs changing, as it gives you a lot more work.

At this point the fact that you want a combo box is immaterial. You need to get the data organised before you start adding forms.

One big decision is whether you need separate tables for tools and ingredients. If you can put everything in a single table, it might be easier to design the database. Just have a flag to distinguish a tool from an ingredient. It may work, but it may not, but it's worth considering.

I hope this helps.
Good luck.
Yes, you're completely right. Truth said, when this project was started, there were less requirements, so the design was simple, but as it started to grow more and more complex, I added fixes here and there as they were needed.

Thank you very much for your help. I'll try to check if this works :D
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top