How To Use For...Each On A Recordset

Rood67 · September 18th, 2009, 02:03 PM

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

mmcdonal · October 16th, 2009, 08:52 AM

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