MS ACCESS 2010  VBA loop through multiple recordsets and update a recordset with a value

Posted on 2013-09-11
Medium Priority
Last Modified: 2013-09-12
Could you give me an example on how to set and loop through multiple recordset

Set  Recordset1 = Select Statement

Loop through Recordset1
    set Recordset2 = Select from table where column = Recordset1.column
    Loop through RecordSet2
       concatenate Recordset2.column into hold_variable
         hold_variable  =hold_variable+ "," +  RecordSet2.column
    End loop
    'How do I set up a recordset to update a value in a table?
    Update a column in table with hold_variable
 End loop
Question by:cookiejar
1 Comment
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 39485495
dim rs1 as dao.recordset, rs2 as dao.recordset
'open recordset1

set rs1=currentdb.openrecordset("select * from table1")

'loop recordset1

do until rs1.eof
          'open recordset 2
          set rs2=currentdb.openrecordset("select * from table2 where [column]=" & rs1![column])

          'loop recordset2
          do until rs2.eof

                  hold_variable  =hold_variable & "," &  rs2!column
          'update column in table
         currentdb.execute "update tableX set [columnX]='" & hold_variable & "'", dbfailonerror



Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

586 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question