Keep getting Error 91 For Loop not initialized error

[sportsguy]

Here is the code,

the FOR loop is not recognizing the .EOF,
it used to work correctly, now it doesn't. . what might have changed?

Private Sub MassUpdate_DblClick(Cancel As Integer)


upSQL = "UPDATE DATA INNER JOIN FORECAST ON (DATA.YYYYMM = FORECAST.YYYYMM) AND (DATA.Year = FORECAST.Year) AND " _
    & "(DATA.District = FORECAST.District) AND (DATA.ProductLine = FORECAST.ProductLine) AND (DATA.Type = FORECAST.Type) AND " _
    & "(DATA.Account = FORECAST.Account) SET FORECAST.OCT = DATA.[OCT], FORECAST.NOV = DATA.[NOV], FORECAST.[DEC] = DATA.[DEC], " _
    & "FORECAST.QTR1 = [DATA].[QTR 1], FORECAST.JAN = [DATA].[JAN], FORECAST.FEB = [DATA].[FEB], FORECAST.MAR = [DATA].[MAR], " _
    & "FORECAST.QTR2 = [DATA].[QTR 2], FORECAST.APR = [DATA].[APR], FORECAST.MAY = [DATA].[MAY], FORECAST.JUN = [DATA].[JUN], " _
    & "FORECAST.QTR3 = [DATA].[QTR 3], FORECAST.JUL = [DATA].[JUL], FORECAST.AUG = [DATA].[AUG], FORECAST.SEP = [DATA].[SEP], " _
    & "FORECAST.QTR4 = [DATA].[QTR 4], FORECAST.FYTOTAL = [DATA].[FISCAL YEAR], FORECAST.LastUpdate = [DATA].[LastSave] " _
    & "WHERE (((FORECAST.ImportTime)<[DATA].[LastSave])); "
 

If IsNull(Me!ComboPaths) Then
    MsgBox "Please Select the folder path in the Drop Down Box", vbCritical, "TYCO SimplexGrinnell"
    DoCmd.GoToControl "ComboPaths"
    Exit Sub
Else
End If
 

On Error GoTo Err_MassUpdate_DblClick
DoCmd.DeleteObject acTable, "DATA"
 
Dim cnnLocal As New ADODB.Connection
Dim rstCurr As New ADODB.Recordset
Set cnnLocal = CurrentProject.Connection
 
Counter = 0
 
rstCurr.Open "SELECT CONTROL.RevisionFileName FROM CONTROL WHERE (((CONTROL.Visible)=Yes));", cnnLocal, adOpenStatic, adLockPessimistic
 
With rstCurr
    Do Until .EOF
    
    
    For Each fldCurr In .Fields
        
        anyFileName = fldCurr.Value
        anyPath = Forms!MassImport.[ComboPaths] & "\" & anyFileName & ".xls"
        
    DoCmd.SetWarnings False
        
        If Dir(anyPath) <> "" Then
            Debug.Print anyPath
            Counter = Counter + 1
            On Error GoTo SecondTry
            DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "DATA", anyPath, True, "ACCESSPASTE"
            DoCmd.RunSQL upSQL
            DoCmd.DeleteObject acTable, "DATA"
            GoTo Done
SecondTry:
            DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "DATA", anyPath, True, "ACCESS!$A$1:$X$341"
            DoCmd.RunSQL upSQL
            DoCmd.DeleteObject acTable, "DATA"
            
Done:
      
        End If
    
    Next
 
    .MoveNext
 
    Loop
 
End With
 
rstCurr.Close
Set cnnLocal = Nothing
Set rstCurr = Nothing
 

MsgBox Counter + "Available Files Updated Successfully", vbOKOnly, "TYCO Simplex Grinnell"
 
 
 
Exit_MassUpdate_DblClick:
    DoCmd.SetWarnings True
    Exit Sub
 
Err_MassUpdate_DblClick:
    If Err.Number = 7874 Then
        Resume Next
    ElseIf Err.Number = 13 Then
        Resume Done
    Else
        MsgBox Err.Number & " - " & Err.Description
        Debug.Print "Error File" & anyPath & " - " & Err.Number & " - " & Err.Description
        GoTo Exit_MassUpdate_DblClick
    End If
    
End Sub

thanks

sportsguy

 

[modest]

Technically the For loop has nothing to do with the .EOF
It is the Do Loop that would not be recognizing it. It looks fine, though, try and compile.

It could be that when it reaches the .EOF it is getting an error and going back to the SecondTry label. You might want to put another "On Error Goto", but really you shouldnt put the error handler in the middle of your code. A Resume statement may be needed.

 

[gemma-the-husky]

maybe its your rstcurr.open statement returning nothing, in which case the for loop will fail, probably with the message you get

 

[MicroE]

Try adding the Option Explicit statement to the module and declare all the variables.
Dim Counter As Long
Dim fldCurr As Field

A more efficient method of exporting the data to excel would be to create a query and export the query. Running an update query and deleting table will cause database bloat.

Here is an example of a database that exports report date via a query:
http://www.myaccesstips.com/UI/Reports.html

Here is code to modify the SQL of a query.
http://www.myaccesstips.com/SearchDetails.asp?TipID=1