Using the For ..Next Loop

[stretch]

I'm really struggling with this - and have been for about 4 days !!

I have a query called SuppList that contains two fields - supplier and Suppname and has 101 records

I need to loop through this query and use the supplier in each record as the criteria in another query and then output this second query to Excel.

I've got most of it but I'm struggling with the loop part.

Please can anyone help ???

Here is the code so far : (PS - I had this all sorted, but lost my HDD in December - and you guessed it - no back up - serves me right I know )

------------------------------------------------------------------
Function dataex3()
On Error GoTo testExport_Err

Dim strDate As String
Dim strFileName As String
Dim strFilePath As String
Dim strSupp As String
Dim intsupp As Integer
Dim intcnt As Integer
Dim Supplier As Variant

Dim Supplist As DAO.Recordset

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SuppList")
Supplier = rs.Fields("Supplier")

intcnt = 0
intsupp = DCount("[SuppList]![Supplier]", "[SuppList]")

strDate = CStr(DatePart("yyyy", Date) & "_" & (DatePart("m", (Date - 1))))
strFileName = strSupp & strDate & ".xls"
strFilePath = "O:\transferord\test\" & strFileName


For intcnt = 1 To intsupp

DoCmd.OpenQuery "SuppList", acNormal, acReadOnly
Set rs = db.OpenRecordset("SuppList")


For Each Supplier In Supplist
strSupp = Supplier
DoCmd.OpenQuery "CodeInvData", acNormal, acReadOnly
DoCmd.TransferSpreadsheet acExport, 8, "CodeInvData", strFilePath, True, ""

Next Supplier


Next


----------------------------------------------------------

THanks

 

[Mcgrco]

Just a guess but should it be next intcnt instead of supplier

 

[stretch]

Thanks for the quick response, but that's not it - this is a nested loop - the second Next you can see refers to the intcnt,

The first one is correct .

Thanks again

 

[WayneRyan]

Stretch,

I put "***' by a couple of things that I see wrong:


' **************************************************
Function dataex3()
On Error GoTo testExport_Err

Dim strDate As String
Dim strFileName As String
Dim strFilePath As String
Dim strSupp As String
Dim intsupp As Integer
Dim intcnt As Integer
Dim Supplier As Variant

Dim Supplist As DAO.Recordset

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SuppList")
Supplier = rs.Fields("Supplier")

intcnt = 0
intsupp = DCount("[SuppList]![Supplier]", "[SuppList]")

strDate = CStr(DatePart("yyyy", Date) & "_" & (DatePart("m", (Date - 1))))
'
' *** strSupp has no value yet ***
'
strFileName = strSupp & strDate & ".xls"
strFilePath = "O:\transferord\test\" & strFileName


For intcnt = 1 To intsupp

DoCmd.OpenQuery "SuppList", acNormal, acReadOnly
Set rs = db.OpenRecordset("SuppList")

'
' *** Supplist is not a populated recordset
' it is only declared ***
'
For Each Supplier In Supplist
strSupp = Supplier
DoCmd.OpenQuery "CodeInvData", acNormal, acReadOnly
DoCmd.TransferSpreadsheet acExport, 8, "CodeInvData", strFilePath, True, ""

Next Supplier


Next
' **************************************************

Wayne

 

[stretch]

Wayne

Thanks for your insights - much appreciated - I don't really get this whole recordset thing - I've not used it before, I thought when I set rs = db.openrecordset("SuppList") it populated the recordset from the supplist query.

How do I populate it then??

Also, I set the value of strSupp within the For Loop as I wanted it to export a different filename for each supplier. do I need to bring the strFilename = ......... down within the for loop as well ?

Thanks again for the help

 

[Mcgrco]

Not getting too involved but this how I populate recordsets.

Basicaly use a normal query to select the results you want , Convert it to sql and follow the structure as below. if you want to send me the sql view once you have set up the query then I will show you how I would open it as a recordset in code.

strSql = strSql & " "
strSql = strSql & " SELECT [tbl ref Forecast Working].TempDate, [tbl ref Forecast Working].[SumOfTotal£],[tbl ref Forecast Working].NewTotal"
strSql = strSql & " FROM [tbl ref Forecast Working]"
strSql = strSql & " ORDER BY [tbl ref Forecast Working].TempDate;"


Set rs = db.OpenRecordset(strSql)

 

[stretch]

Thanks

I'll give that a go !