访问 VBA：将多选列表框的值放入表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26305379/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access VBA: Put values of a multi-select list box into a Table
提问by SaladSnake
I have a list box where I can select Monday- Friday. I can select as many days as I want in the list box, all of them if i wanted. I want to know how to insert the value of the listbox into my table.
我有一个列表框，我可以在其中选择周一至周五。我可以在列表框中选择任意天数，如果我愿意，可以选择所有天数。我想知道如何将列表框的值插入到我的表中。
Here's the code I've written so far:
这是我到目前为止编写的代码：
Private Sub Command499_Click()
Set RstRecSet = Nothing
Set db = CurrentDb
Dim dateDay As String
Dim dateWeek As String
MsgBox (lstDateDay.Selected)
''dateWeek = lstDateWeek.Value
db.Execute " INSERT INTO tblContacts (DateDay, DateWeek)Values" & "('" & dateDay & "', '" & dateWeek & "');"
db.Close
End Sub
As you can see I've been trying a lot of different things. My problem is getting the value of the list box; it keeps showing as null even though it has data selected. The exact error i'm getting is: "Invalid use of Null."
正如你所看到的，我一直在尝试很多不同的东西。我的问题是获取列表框的值；即使它选择了数据，它仍然显示为空。我得到的确切错误是：“无效使用 Null。”
Let me know what you think.
让我知道你的想法。
Thanks, Salad
谢谢，沙拉
EDIT:
编辑：
Set rs = db.OpenRecordset("tblContacts")
For Each itm In lstDateWeek.ItemsSelected
rs.AddNew
rs!dateWeek = lstDateWeek.ItemData(itm)
rs!dateDay = itm
rs.Update
Next
rs.Close
Set rs = Nothing
Set db = Nothing
dateDay and dateWeek are columns in tblContacts.
dateDay 和 dateWeek 是 tblContacts 中的列。
采纳答案by David
You need to use the ItemsSelected collection to get the index of the selected items in the Multi Select list box and then iterate through them and use the index to reference the rows stored in the ItemData collection. As part of this iteration simply create a record set and add the fields and update. There are different ways to handle this part but I like this one shown below.
您需要使用 ItemsSelected 集合来获取多选列表框中所选项目的索引，然后遍历它们并使用索引来引用存储在 ItemData 集合中的行。作为此迭代的一部分，只需创建一个记录集并添加字段并进行更新。有不同的方法来处理这部分，但我喜欢下面显示的这个。
To use my sample, simply create a table called tblTest and two columns Description (text) and Day as a number. Create a form and add a multi-select list box named DaysOfWeek. fill it in with the days of the week as a ValueList and then add a button which I labeled Store.
要使用我的示例，只需创建一个名为 tblTest 的表和两列 Description (text) 和 Day 作为数字。创建一个窗体并添加一个名为 DaysOfWeek 的多选列表框。用一周中的几天作为 ValueList 填充它，然后添加一个我标记为 Store 的按钮。
Paste the following code into the buttons click event and try it
将以下代码粘贴到按钮点击事件中并尝试
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblTest")
For Each itm In DaysOfWeek.ItemsSelected
rs.AddNew
rs!Description = DaysOfWeek.ItemData(itm)
rs!Day = itm
rs.Update
Next
rs.Close
Set rs = Nothing
Set db = Nothing
My event procedure looks like this:
我的事件过程如下所示：
Private Sub Command19_Click()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblTest")
For Each itm In DaysOfWeek.ItemsSelected
rs.AddNew
rs!Description = DaysOfWeek.ItemData(itm)
rs!Day = itm
rs.Update
Next
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
This could be done with an ADO call by building a SELECT string for your INSERT statement as well but for me this is straightforward.. If you have any questions let me know. If I can figure out how to attach my sample database I will.
这可以通过 ADO 调用来完成，方法是为您的 INSERT 语句构建一个 SELECT 字符串，但对我来说这很简单。如果您有任何问题，请告诉我。如果我能弄清楚如何附加我的示例数据库，我会的。
