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

Comments

Post a Comment

Popular posts from this blog

python - Subclassed QStyledItemDelegate ignores Stylesheet -

-
i'm trying subclass qstyleditemdelegate remove focus rectangle in qcombobox . even though i'm calling base implementation of paint function , nothing else, result different. looks if parts of style-sheet influence bounding box of item taken consideration. class pstyleditemdelegate(qstyleditemdelegate): def __init__(self, *args, **kwds): super(pstyleditemdelegate, self).__init__(*args, **kwds) def paint(self, *args, **kwargs): qstyleditemdelegate.paint(*args, **kwargs) what have paint unmodified qstyleditemdelegate ? as suggested tried replacing pyside pyqt4 , works, appears bug. update pyside 1.1.2 1.2.1 result same. unfortunately switch breaks other parts of code, if there no other suggestions i'm going accept answer. edit bug tracked here
Read more

java - HttpClient 3.1 Connection pooling vs HttpClient 4.3.2 -

-
i have used httpclient3.1(java) connection pooling below : import org.apache.commons.httpclient.httpclient; import org.apache.commons.httpclient.multithreadedhttpconnectionmanager; import org.apache.commons.httpclient.params.httpclientparams; import org.apache.commons.httpclient.params.httpmethodparams; import org.apache.log4j.logger; public class httpclientmanager { private static logger logger = logger.getlogger(httpclientmanager.class); private static multithreadedhttpconnectionmanager connectionmanager = new multithreadedhttpconnectionmanager(); private static httpclientparams clientparams = new httpclientparams(); static { try { clientparams.makelenient(); clientparams.setauthenticationpreemptive(false); clientparams.setparameter(httpmethodparams.so_timeout, new integer(30000)); // set so_timeout clientparams.setparameter(httpmethodparams.head_body_check_timeout, new integer(3000)); // head responses ...
Read more

node.js - StackOverflow API not returning JSON -

-
i using node.js script gather data stackoverflow. know responses gzipped, put code in should take care of that. script follows: var request = require('request'); var zlib = require('zlib'); function getstackoverflowresponse(url, callback){ request(url, {encoding: null}, function(err, response, body){ if(response.headers['content-encoding'] == 'gzip'){ zlib.gunzip(body, function(err, dezipped) { callback(dezipped); }); } else { callback(body); } }); } var url = "https://api.stackexchange.com/docs/questions#pagesize=2&order=desc&min=2014-01-04&max=2014-02-02&sort=activity&tagged=apigee&filter=default&site=stackoverflow&run=true"; getstackoverflowresponse(url, function(questions) { console.log(questions); }); instead of getting json output, i'm getting following response: buffer 0d 0a 0d 0a 0d 0a 0d 0a 3c 21 44 4f 43 54 59 50 45 20 48...
Read more