Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 18th, 2009, 02:03 PM
Authorized User
 
Join Date: Mar 2004
Location: Knoxville, TN, USA.
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Rood67
Question How To Use For...Each On A Recordset

Hi. With some great help from Mac here on this forum and a few post on some others, I was able to work out connected to AS400 through VBA and pull data into a recordset. See the issue and solution here... VBA Passthrough Query


Now I have come across a new problem. I need to know how to take unique item codes that I pull from one table, and step through another table using the first as the parameter for the second. Wow, that sounds confusing.

Here is what I'm thinking... I know this is not the proper code, just best guess at trying to show you what I'm wanting to do.

Code:
Dim rsUnique, rsItemQty as ADODB.Recordset
Dim tblUnique, tblSource, sqlUnique as String
Dim lgSum6MQ as Long 'dim on up to lgSum24MS

tblUnique = "tbl_UniuqueItemCodes"
tblSource = "tbl_cupmmof"

sqlUnique = "SELECT * FROM " & tblUnique

Set rsUnique = New ADODB.Recordset
Set rsItemQty= New ADODB.Recordset

rsUnique.Open sqlUnique, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rsItemQty.Open "tblItemQty", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Now, it gets into things I have no idea about, only seen other post with something touching on this.

rsItemQty.MoveFirst
For each item in rs.Unique
  lgSum6MQ = customFunction6MQ(item) 'calculate quantity for 6 months
  lgSum6MS = customFunction6MS(item) 'calculate sales for 6 months
'repeat for 12 month, 18 months, and 24 months
  rsItemQty.AddNew
  rsItemQty.Fields("ItemCode") = item
  rsItemQty.Fields("6MQ") = lgSum6MQ
  ...
  ...
  rsItemQty.Fields("24MS") = lgSum24MS
  rsItemQty.Update
Next

Set rsUnique = Nothing
Set rsItemQty = Nothing
Oh, I know that is so wrong and overly simplified, but hopefully it will aid one of you wise people in deciphering what it is that I'm trying to do. I need to take the item code from a table that I created that has unique item codes in it, and run a series of calculation functions on a subset of data based on each item code.

I managed to do this in a select query that I changed into a make table query but using the unique item code table as my source table, then each of the other columns in the query are calculated fields using the item code in the first column as their source.

I also want to be able to populate a combo box with the unique items, have a check box group available, and let the end user decide on what calculations they want performed on what item code. And from that return the calculated value to a text box on a form.

And if that wasn't enough, be able to take the output of the for...each and use it as the data source for a report, if that's even possible. I saw examples of tying a recordset to the data source so I think I can manage that.

TIA
__________________
<b>Rood67</b>
Reply With Quote
  #2 (permalink)  
Old October 16th, 2009, 08:52 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

That is pretty much how you want to do this. I would use this sort of loop though:

Code:
Do Until rsUnique.EOF
   lgSum6MQ = customFunction6MQ(item) 'calculate quantity for 6 months
   lgSum6MS = customFunction6MS(item) 'calculate sales for 6 months
'repeat for 12 month, 18 months, and 24 months
    rsItemQty.AddNew
    rsItemQty.Fields("ItemCode") = item
    rsItemQty.Fields("6MQ") = lgSum6MQ
    ...
    ...
    rsItemQty.Fields("24MS") = lgSum24MS
    rsItemQty.Update
 
rsUnique.MoveNext
Loop
__________________
mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Clone DAO Recordset into ADO Recordset kamrans74 VB How-To 0 March 6th, 2007 11:57 AM
Query from Recordset into another Recordset kamrans74 Pro VB Databases 5 March 5th, 2007 04:17 PM
ADODB.Recordset (0x800A0CB3)Current Recordset does tks_muthu Classic ASP Databases 0 June 16th, 2005 07:22 AM
Convert ADO recordset to DAO recordset andrew_taft Access 1 May 5th, 2004 02:31 PM
Recordset KennethMungwira ADO.NET 1 January 12th, 2004 03:54 PM



All times are GMT -4. The time now is 08:39 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.