MS Access VBA 循环通过列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22149499/
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
MS Access VBA loop through list
提问by kvibbertj
I think this should be simple, but I can't find the right way to do it. I have a couple examples of the situation I'm trying to resolve, so I'll describe both. I think they likely can be handled with the same solution, but if not, let me know. I'm trying to loop through a list of values, and run a SQL query for each one. In one situation, the values are text, and in another, they are non-sequential integers.
我认为这应该很简单，但我找不到正确的方法。我有几个我正在尝试解决的情况的例子，所以我将描述两者。我认为它们可能可以用相同的解决方案来处理，但如果不是，请告诉我。我正在尝试遍历值列表，并为每个值运行 SQL 查询。在一种情况下，这些值是文本，而在另一种情况下，它们是非连续整数。
Edited to add: I should have said that the list of values is dynamic, it is coming from list of ID values selected in a combo box. Right now, those are being stored as a comma delimited list in my variable named item_in_my_text_list and item_in_my_ID_list below.
编辑添加：我应该说值列表是动态的，它来自组合框中选择的 ID 值列表。现在，这些以逗号分隔的列表形式存储在我下面名为 item_in_my_text_list 和 item_in_my_ID_list 的变量中。
Something like this:
像这样的东西：
item_in_my_text_list = "User-defined, mean, upper-bound"
For Each item_in_my_text_list(i)
'run a query where i is part of the condition
'I want to run the same query first the user-defined metric, then for the mean, then for the upper-bound
'SQL should be "select metric, age, value into scenario where metric = 'user-defined'"
SQL= "Select metric, age, value into scenario WHERE metric = [i]
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
SQL = ""
rs.MoveNext
Loop
The other situation involves a non-sequential numeric list of ID values:
另一种情况涉及 ID 值的非顺序数字列表：
item_in_my_ID_list = "7, 9, 15, 88"
For Each item_in_my_ID_list(i)
'run a query where i is part of the condition
'I want to run the same query first for 7, then for 9, then 15, and so on
SQL= "Update thistable set x = y where ID = " & [i]
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
SQL = ""
rs.MoveNext
Loop
What is the right syntax for setting up the loop? When I try to use Split, it seems like it gives me the count of the items in the list, so when I try to use [i] as part of the query condition, I get, 1, 2, 3 (depending on the number of items in the list), rather than 'user-defined' or the correct ID value.
设置循环的正确语法是什么？当我尝试使用 Split 时，它似乎给了我列表中项目的数量，所以当我尝试使用 [i] 作为查询条件的一部分时，我得到 1、2、3（取决于列表中的项目数），而不是“用户定义”或正确的 ID 值。
Edited to add my solution. The suggestion from @mehow gave me what I needed to make this work.
编辑以添加我的解决方案。@mehow 的建议给了我完成这项工作所需的一切。
Set db = CurrentDb
'Delete records for algorithms that aren't selected
Dim DeleteSQL As String
DeleteSQL = "Delete from Scenario WHERE ID not in (" & Me.ListSelect & ")"
db.Execute DeleteSQL, dbFailOnError
DeleteSQL = ""
'for each of the IDs selected, copy the whole scenario
Dim i As Long
For i = 0 To UBound(Split(Me.ListSelect, ","))
'find the records from the scenario table, and copy them for the "upper bound"
SQL = "insert into scenario (a, b, c)"
SQL = SQL & " SELECT a, b, c
SQL = SQL & " FROM Scenario "
SQL = SQL & " WHERE Scenario.Id= " & Trim(Split(Me.ListSelect, ",")(i))
db.Execute SQL, dbFailOnError
SQL = ""
'reset user-defined values for both mean and upper bound
Dim allMetrics As String
allMetrics = "Central tendency, Upper bounding"
Dim thisMetric As Long
For thisMetric = 0 To UBound(Split(allMetrics, ","))
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT Parameter FROM AllParameters WHERE Id= " & Trim(Split(Me.ListSelect, ",")(i)))
Dim DefaultSQL As String
If Not (rs.EOF And rs.BOF) Then
Do Until rs.EOF = True
DefaultSQL = "UPDATE defaults LEFT JOIN scenario ON defaults.Age = Scenario.Age SET Scenario." & rs("Parameter") & " = [defaults].[Value] "
DefaultSQL = DefaultSQL & " WHERE defaults.ID =" & Trim(Split(Me.ListSelect, ",")(i))
DefaultSQL = DefaultSQL & " And defaults.metric = '" & Trim(Split(allMetrics, ",")(thisMetric)) & "'"
db.Execute DefaultSQL, dbFailOnError
DefaultSQL = ""
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
Next
Next
回答by Bathsheba
For Each i In VBA.Split(item_in_my_ID_list, ",")is one way. iis a string type.
For Each i In VBA.Split(item_in_my_ID_list, ",")是一种方式。i是字符串类型。
You might have to use VBA.Trim(i)to cater for spaces between the commas.
您可能必须使用VBA.Trim(i)来处理逗号之间的空格。
回答by Bathsheba
I think the below is the syntax you may be looking for
我认为以下是您可能正在寻找的语法
Sub Main()
Dim myList As String
myList = "7, 9, 15, 88"
Dim i As Long
For i = 0 To UBound(Split(myList, ","))
SQL = "Select metric, age, value into scenario " & _
"WHERE metric = " & Trim(Split(myList, ",")(i))
' the rest of your code...
Next
End Sub
回答by E Mett
You need to use an array and then loop through it.
您需要使用一个数组，然后循环遍历它。
Dim str(2) As String
Dim i as Integer
str = Array("one", "two", "three")
For i = 0 To UBound(str)
...str(i)
Next
