CODEWITHSUNDEEP
exceleofvba
Brian
Brian

Reputation: 7

Either BOF or EOF is True. Include EOF in Do Until

I'm getting an error at rs.Movenext in my For Each. I've tried using a Do Until but not sure where to place it. Can someone advise where to place it? Not sure if using EOF in my Do Until will overcome the error though.

Thanks

    Sub FindCardOrdersv2()

    ' Initialize variables.
    Dim cn As ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim provStr As String
    Dim intMaxCol As Integer
    Dim intMaxRow As Integer
    Dim rsFilter As Range
    Dim i As Integer
    Dim rng As Variant
    Dim payid(1 To 10) As String
    Dim tw As ThisWorkbook
    Dim errmsg As String

    Workbooks("cleanse.xlsm").Activate
    Worksheets("Sheet1").Activate

    ' Create new instances
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset


    ' sql query
    sql = "SELECT TOP 100 t.tri_transactionidcode," _
          & "SUBSTRING(t.tri_reference, 1, 9) AS merchantref," _
          & "t.tri_additionalreferencenumber, t.CreatedOn, t.tri_amount, ISNULL(t.tri_paymenttransactiontypeidName, 'Online')" _
          & " FROM dbo.tri_onlinepayment t INNER JOIN dbo.tri_transaction tr ON tr.tri_onlinepaymentid = t.tri_onlinepaymentId" _
          & " WHERE t.tri_transactionresult = 9 AND t.tri_transactionidcode IN (1013302661,1013327345, 1013172653)"



    ' Specify the OLE DB provider.
    cn.Provider = "sqloledb"

    ' Specify connection string on Open method.
    cn.Open "Data Source=IFL-SQL11;Database=IFL_MSCRM;Trusted_Connection=yes;Integrated Security=SSPI"



    ' Assign active connection to recordset
    Set rs.ActiveConnection = cn
    'intMaxCol = rs.Fields.Count

    ' Define cursors and open sql
    With rs
        .CursorLocation = adUseServer
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
        .Open sql
    End With


    For i = 1 To rs.RecordCount
       If Not (rs.BOF And rs.EOF) Then
            payid(i) = rs.Fields.Item(0)
            Debug.Print rs(0)
            Debug.Print rs(1)
            Debug.Print rs(3)
        End If
        rs.MoveNext
    Next i

    errmsg = "No matches found"

    For Each rsFilter In Range("A1:A10").Cells
        For i = 1 To rsFilter.Cells.Count
          'Do Until rs.EOF
            If rsFilter.Value = payid(i) Then
                   Debug.Print rsFilter.Value
                   Debug.Print rsFilter.Offset(0, 1).Value
            Else: Debug.Print errmsg & " " & rsFilter.Value

            End If
              'Loop
                    rs.MoveNext
            'Loop
        Next i
    Exit For

Update

Following your suggestion Fil, I'm getting an error at bold. Do I not need to use i as a counter? Sorry I'm a little confused because its the second For Each that I can't get to work properly. The first For works fine as far as I know. Thanks

'For i = 1 To rs.RecordCount
    'If Not (rs.BOF And rs.EOF) Then
    While Not rs.EOF
         **payid(i) = rs.Fields.Item(0)**
         Debug.Print rs(0)
         Debug.Print rs(1)
         Debug.Print rs(3)
     End If
     rs.MoveNext
   End While
'Next i


    **For Each rsFilter In Range("A1:A10").Cells
        For i = 1 To rsFilter.Cells.Count
          'Do Until rs.EOF
            If rsFilter.Value = payid(i) Then
                   Debug.Print rsFilter.Value
                   Debug.Print rsFilter.Offset(0, 1).Value
            Else: Debug.Print errmsg & " " & rsFilter.Value

            End If
              'Loop
                    rs.MoveNext
            'Loop
        Next i
    Exit For
    Next**

Update 2

I've fixed the BOF and EOF error I had at first by doing a a while not rs.eof before rs.movenext. But when my If is true (barclays.value = payid(i) I get another BOF/EOF is true error when I try to enter the rs.fields.item(0) back to my range. Any suggestions? Thanks

For Each barclays In Range("A1", Range("A1").End(xlDown)).Cells 
    For i = 1 To rs.RecordCount 
        If barclays.Value = payid(i) Then 
           barclays.Offset(0, 1) = rs.Fields.Item(0)
            Debug.Print barclays.Value 
            Debug.Print barclays.Offset(0, 1).Value 
        Else: 
            barclays.Offset(0, 1) = "No payment found for " & barclays.Value 
            Debug.Print "No payment found for " & barclays.Value & " for " & payid(i) 
        End If 
        While Not rs.EOF 
            rs.MoveNext 
        Wend 
    Next i 
Next

Upvotes: 0

Views: 7693

Answers (1)

Fil
Fil

Reputation: 1060

I would change:

    'For i = 1 To rs.RecordCount
       'If Not (rs.BOF And rs.EOF) Then
       While Not rs.Eof
            payid(i) = rs.Fields.Item(0)
            Debug.Print rs(0)
            Debug.Print rs(1)
            Debug.Print rs(3)
        End If
        rs.MoveNext
      End While
    'Next i

The second for doesn't nothing on the recordset so you have to remove "rs.Movenext" instruction. The "Exit For" outside a For is meaningless, so you have to remove that instruction too.

Upvotes: 1

Related Questions