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):
Other properties are:
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?
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
- Table RelsRecipesIngredients: RecipeIDFK, IngredientIDFK
- Table RelsRecipesCookingtools: RecipeIDFK, ToolIDFK
Code:
SELECT DISTINCT CookingTools.ToolID, CookingTools.ToolName FROM CookingTools ORDER BY CookingTools.ToolName;
- Number of columns: 2
- Column width: 0cm;4cm
- Bound column: 1
- Limit to list: Yes
- Allow value list edits: Yes
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?