title: VBA - Loop through x items in blocks of 100 x times
body: <p>I've been struggling with this for a day now.
  I have a list box that is populated with x items. Could be 1 - x</p>
  
  <p>I need to take all the items in the list box and format them into a string
  which I submit into an oracle database.  I'm using INLIST on the SQL side and because of that I can only have a maximum of 100 items in my string.</p>
  
  <p>So for example if I was to have 547 items in the listbox, I would iterate through all 547 items, but at every 100 I would submit into the database, returning my result into my collection class, finishing with the last 47.</p>
  
  <p>here's what i have so far.  There is some attempts to solve my problem in the code so if it's confusing i'll try to explain.</p>
  
  <pre><code>Public Function SearchBMS()
  On Error GoTo HandleError
  
      Dim rst As ADODB.Recordset
      Dim sESN As String
      Dim i As Integer
      Dim x As Integer
      Dim maxrec As Integer
      Dim itemcnt As Integer
      Dim iBlockCount As Integer
  
      With frmEngineCampaignSearch.lstbxESNNumbers
          itemcnt = .ListCount - 2
          'iBlockCount = GetBlockCount(itemcnt)
              x = 0
              maxrec = 100
              Debug.Assert itemcnt = 200
  
              For i = 0 To itemcnt
                  For x = i To maxrec
                      MsgBox "test", vbOKOnly
                  i = i + 100
                  Next x
                      If i = itemcnt Then ' if I = last item than we put the closing parenthesis on our string
                          sESN = sESN &amp; "'" &amp; .list(i) &amp; "'"
                      Else
                          sESN = sESN &amp; "'" &amp; .list(i) &amp; "' , " ' otherwise there are more items so we seperate by comma
                      End If
  
                      If itemcnt &lt;= 100 Then
                          Set rst = Nothing
                          'Set rst = rstGetCustomerInfo(sESN)
                          'LoadRSTToCollection rst
                      elseif
  
                      While x = maxrec
                          MsgBox "submit first 100", vbOKOnly
                          'Set rst = Nothing
                          'Set rst = rstGetCustomerInfo(sESN)
                          'LoadRSTToCollection rst
                          sESN = gC_sEMPTY_STRING
                          maxrec = maxrec + 100
                      Wend
                      x = x + 1
  
              Next i
      End With
  HandleError:
  If Err.Number &gt; 0 Then
  MsgBox Err.Number &amp; ": " &amp; Err.Description
  
  End If
  </code></pre>
  
  <p>This function is to get the number of times I would have to perform the submission but i hit a road block on how to use it within the for loop</p>
  
  <pre><code>Public Function GetBlockCount(ByRef lItemCnt As Long) As Integer
  Dim x As Double
  If lItemCnt &lt;= 100 Then
      GetBlockCount = 1
      Exit Function
  ElseIf lItemCnt &gt; 100 Then
      x = Round(lItemCnt / 100)
      If lItemCnt Mod 100 &gt; 0 Then
          x = x + 1
      Else
      GetBlockCount = x
      Exit Function
      End If
  End If
  End Function
  </code></pre>
  
  <p>Any help would be much appreciated. </p>