VBA - Loop through x items in blocks of 100 x times -

i've been struggling day now. have list box populated x items. 1 - x

i need take items in list box , format them string submit oracle database. i'm using inlist on sql side , because of can have maximum of 100 items in string.

so example if have 547 items in listbox, iterate through 547 items, @ every 100 submit database, returning result collection class, finishing last 47.

here's have far. there attempts solve problem in code if it's confusing i'll try explain.

public function searchbms() on error goto handleerror      dim rst adodb.recordset     dim sesn string     dim integer     dim x integer     dim maxrec integer     dim itemcnt integer     dim iblockcount integer      frmenginecampaignsearch.lstbxesnnumbers         itemcnt = .listcount - 2         'iblockcount = getblockcount(itemcnt)             x = 0             maxrec = 100             debug.assert itemcnt = 200              = 0 itemcnt                 x = maxrec                     msgbox "test", vbokonly                 = + 100                 next x                     if = itemcnt ' if = last item put closing parenthesis on our string                         sesn = sesn & "'" & .list(i) & "'"                     else                         sesn = sesn & "'" & .list(i) & "' , " ' otherwise there more items seperate comma                     end if                      if itemcnt <= 100                         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     end handleerror: if err.number > 0 msgbox err.number & ": " & err.description  end if 

this function number of times have perform submission hit road block on how use within loop

public function getblockcount(byref litemcnt long) integer dim x double if litemcnt <= 100     getblockcount = 1     exit function elseif litemcnt > 100     x = round(litemcnt / 100)     if litemcnt mod 100 > 0         x = x + 1     else     getblockcount = x     exit function     end if end if end function 

any appreciated.

i think need clean out , make more readable. @ , solution clearer.

here simple skeleton of should like:

i = 100 txt = get100requests(i) while txt <> ""   'use txt   = + 100   txt = get100requests(i) loop  function get100requests(fromitem integer) string   if fromitem => frmenginecampaignsearch.lstbxesnnumbers.listcount exit function   dim integer   + fromitem   while < fromitem + 99 , < frmenginecampaignsearch.lstbxesnnumbers.listcount     get100requests = get100requests & "'" & frmenginecampaignsearch.lstbxesnnumbers.list(i) & "', "      = + 1   loop   get100requests = left(get100requests, len(get100requests)-2) exit function