将整个数组传递给查询 [英] Passing an entire array to a query
问题描述
我正在尝试学习Access和VBA，所以请保持温和。 :)我用一个食谱表（tblRecipes）构建了一个数据库，其中包含食谱名称，营养信息，菜肴类型（早餐，午餐等），以及几个成分列。
然后我用一个组合框创建了一个表单（每周菜单），允许用户为该周的菜单选择食谱。
现在我想创建一个查询，返回菜单中与配方选项对应的配方表中的成分。此查询将成为购物清单的基础，并通过单击每周菜单表单上的按钮控件来调用。
我创建了一个数组（Dim ThisWeekMenu（1 To 49）As String ）从组合框选择中保存配方名称（其中一些可能为空）。数组在组合框的After_Update过程中填充：
Private Sub B1_AfterUpdate（）
B1_Cals = DLookup（" [Calories]"， " [[Breakfast_Query]"，[Recipe_Name] ="&" [B1]"）
ThisWeekMenu（1）= [B1]
结束Sub
有没有办法将整个数组传递给查询的Criteria属性中的查询？我可以使用以下方式一次传递一个项目：
=表格！[每周菜单]！[B1]
但我可以''弄清楚如何传递整个阵列。
提前感谢您的帮助！
I''m trying to learn Access and VBA, so please be gentle. :) I constructed a database with a table of recipes (tblRecipes), containing the recipe name, nutrition info, type of dish (breakfast, lunch, etc.), and several columns for ingredients.
Then I made a form (Weekly Menu) with combo boxes that allow the user to select recipes for that week''s menu.
Now I''d like to create a query that returns the ingredients from the recipe table corresponding to the recipe selections on the menu. This query will be the foundation of a grocery list and is called by clicking a button control on the Weekly Menu form.
I created an array (Dim ThisWeekMenu(1 To 49) As String) to hold the recipe names from the combo box selections (some of which may be null). The array is populated in the After_Update procedures for the combo boxes:
Private Sub B1_AfterUpdate()
B1_Cals = DLookup("[Calories]", "[Breakfast_Query]", "[Recipe_Name]=" & "[B1]")
ThisWeekMenu(1) = [B1]
End Sub
Is there a way to pass the entire array to the query in the query''s Criteria properties? I can pass one item at a time using:
=Forms![Weekly Menu]![B1]
but I can''t figure out how to pass the whole array.
Thanks in advance for any help!
推荐答案
很抱歉不回答您的问题，但根据您的意见判断，你的桌子需要规范化。
你需要5个桌子，虽然你可以逃脱4.
Sorry not to answer your question, but judging from what you said, your tables need normalising.
You need 5 tables although you can get away with 4.
感谢您的回复！对不起，我没有提到我确实在单独的成分表中有成分，另一个表格用于菜肴类型（主菜，小吃，午餐等），另一个用于过道或部门可以找到原料的杂货店。我试图对表格之间的关系非常小心，所以我不必多次输入相同的信息。
仍然希望（简单？ ）使用菜单中的配方名称将数组传递给构建购物清单的查询的方式...如果可以在查询设计窗口的Criteria部分中完成，那比编写程序要容易，但是我必须以某种方式做到这一点，即使它不是很漂亮。 ：）
Thank you for your reply! I''m sorry I didn''t mention that I do have the ingredients in a separate Ingredients table, another table for the type of dish (entree, snack, lunch, etc.), and another for the aisle or department in the grocery store where the ingredient might be found. I''ve tried to be very careful about the relationships between the tables so I don''t have to enter the same info more than once.
Still hoping for a (easy?) way of passing the array with the recipe names from the menu to the query that builds the grocery list... If it can be done in the Criteria section of the query design window, that would be easier than writing a procedure, but I gotta do it somehow, even if it ain''t pretty. :)
PhilofWalton，
我进一步研究了你的例子，并意识到我没有索引Recipes and Ingredients表。所以我这样做，并且由于你的建议，我确实找到并删除了Ingredients表中的副本。
我不知道这是否是一种可接受的做事方式，但食谱表的组织方式使得每个记录都有食谱名称，每份食物的卡路里数，食物/菜肴类型的查阅栏，以及来自Ingredients表的多个成分查阅列。这样，配方名称只在表格中找到一次。每个食谱的记录都包含该食谱的所有成分。
如果有帮助，我附上了截图。
PhilofWalton,
I studied your examples further and realized I wasn''t indexing the Recipes and Ingredients tables. So I did that, and thanks to your recommendation, I did find and remove a duplicate in the Ingredients table.
I don''t know if this is an acceptable way to do things, but the Recipes table is organized so that each record has the recipe name, the number of calories per serving, a lookup column for the type of meal/dish, and multiple lookup columns of ingredients from the Ingredients table. That way, the recipe name is only found once in the table. Each recipe''s record contains all the ingredients for that recipe.
I''ve attached a screenshot if it helps.
这篇关于将整个数组传递给查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
