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.
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.
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.Fields("ItemCode") = item
rsItemQty.Fields("6MQ") = lgSum6MQ
rsItemQty.Fields("24MS") = lgSum24MS
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.