Solved

Loop and combine records from two tables into third table

Posted on 2013-01-17
2
426 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

713 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