title: MS Access VBA loop through list
body: <p>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.  </p>
  
  <p>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.</p>
  
  <p>Something like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The other situation involves a non-sequential numeric list of ID values:</p>
  
  <pre><code>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 = " &amp; [i]
        DoCmd.SetWarnings False
  
            DoCmd.RunSQL SQL
  
            DoCmd.SetWarnings True
  
            SQL = ""
            rs.MoveNext
  
  Loop
  </code></pre>
  
  <p>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.</p>
  
  <p>Edited to add my solution.  The suggestion from @mehow gave me what I needed to make this work.</p>
  
  <pre><code>Set db = CurrentDb
  'Delete records for algorithms that aren't selected
  
      Dim DeleteSQL As String
      DeleteSQL = "Delete from Scenario WHERE ID not in (" &amp; Me.ListSelect &amp; ")"
  
      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 &amp; " SELECT a, b, c
      SQL = SQL &amp; " FROM Scenario "
      SQL = SQL &amp; " WHERE Scenario.Id= " &amp; 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= " &amp; 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." &amp; rs("Parameter") &amp; " = [defaults].[Value] "
                  DefaultSQL = DefaultSQL &amp; " WHERE defaults.ID =" &amp; Trim(Split(Me.ListSelect, ",")(i))
                  DefaultSQL = DefaultSQL &amp; " And defaults.metric = '" &amp; Trim(Split(allMetrics, ",")(thisMetric)) &amp; "'"
  
  
                  db.Execute DefaultSQL, dbFailOnError
                  DefaultSQL = ""
                  rs.MoveNext
               Loop
  
           End If
  
           rs.Close
           Set rs = Nothing
      Next
  Next
  </code></pre>