VBA Update table/recordset in Access using Loop with values from another table/recordset? -

i need some vba access.

i have table "client_table" 100 rows of data. have table "salesrep_table" have 10 distinct sales rep id numbers (such as: aa1111, , on).

my goal run procedure takes first id record "aa1111" , places in appropriate column on clients table named "assignedsalesrepid" first 10 rows, next id number in salesrep_table gets inserted next 10 cells in clients table, , process repeats through loop until 10 ids in 10 rows each fill 100 rows of data in clients table.

i went creating 2 recordsets , trying loop through sql update. end 100 records containing last sales rep id 100 times repeating. can take @ code , let me know needs fixed?

public sub command01_click()  dim strsql dim clientstablequery, salesreplist dim datab database dim clientqd querydef dim salesqd querydef dim rstclient recordset dim rstsalesrep recordset  clienttablequery = "clients" salestablequery = "salesreplist"  'creates recordset 100 client records named "clients"  strsql = "select * client_table" set datab = currentdb() set clientqd.createquerydef(clienttablequery, strsql) set rstclient = datab.openrecordset(clienttablequery)  'creates recordset 10 sales rep records named "salesreplist" strsql = "select salesrepid salesrep_table" set datab = currentdb() set salesqd.createquerydef(salestablequery, strsql) set rstsalesrep = datab.openrecordset(salestablequery)   rstsalesrep.movefirst rstclient.movefirst  until rstsalesrep.eof = true  'sql query update top 10 cells in "assigned sales rep id" column in   clients recordset sales rep id salesreplist recordset  strsql = "update clients, salesreplist set clients.assignedsalesrepid =    salesreplist.salesrepid clients.clientidnumber in (select top 10      clients.clientidnumber clents clients.assignedsalesrepid null)"  docmd.runsql (strsql) rstsalesrep.movenext  loop msgbox "finished looping" rstsalesrep.close   end sub 

i hate 1 tell this, should reconsider using sql update. see have written lot of code , might feel if switch sql have wasted vb code. have felt myself in times past. can solve problem sql order of magnitude less code(or so).

steps sql solution:

1. sequence rows in both sets
2. mod set sequence b set sequence max
3. update set on mod = b seq