Solved

Loop and combine records from two tables into third table

Posted on 2013-01-17
2
413 Views
Last Modified: 2013-01-17
I have a form with two multi-select list boxes.
1. list box contains months (recordset = tmpInvoiceMth)
2.  list box contains years (recordset = tmpInvoiceYr)

Once the user has chosen their corresponding months and years I would like to loop through both recordsets and add the corresponding Mth-Yr combination into a third recordset (tmpInvoiceMthYr)

EX:  User chooses Jan and Feb from list box 1 and  2012 and 2013 from list box 2
after looping tmpInvoiceMthYr should contain the following records

Jan 2012
Feb 2012
Jan 2013
Feb 2013

Thanks as always for your assistance

~Worcse
0
Comment
Question by:Worcse
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 300 total points
ID: 38789266
dim j, i
rs as dao.recordset

set rs=currentdb.openrecordset("tmpInvoiceMthYr")
for each j in me.listMonth.itemsselected

      for each i in me.listYear.itemsselected
            with rs
                 .addnew
                 !MonthYear=me.listMonth.itemdata(j) & " " & me.listYear.itemdata(i)
                 .update
             end with

      next i
next j
0
 

Author Comment

by:Worcse
ID: 38789452
Cap - spot on as always
Well done sir...!
Thank you for your assistance.
~Worcse

I have attached the code which is now working for me
tmpInvoiceMthYr.doc
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql statement - 9 23
Access 2003 query lost it's only join 7 24
update all email addresses SQL 1 23
Query group by data in SQL Server - cursor? 3 28
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

815 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now