BOF或EOF为True.在Do Until中包含EOF

Question

我得到一个错误在rs.Movenext在我的For Each。我试过使用Do直到，但不确定在哪里放置它。有人能告诉我把它放在哪里吗?不确定是否在我的Do Until中使用EOF会克服这个错误。

感谢

    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

按照你的建议Fil，我得到一个错误在粗体。我不需要将它用作计数器吗?对不起，我有点困惑，因为这是第二次，我不能正常工作。据我所知，第一个For工作得很好。由于

'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**

更新2

我已经修复了BOF和EOF错误，我在rs.movenext之前做了一段时间而不是rs.eof。但当我的If是真的(巴克莱)。当我试图将rs.fields.item(0)返回到我的范围时，我得到另一个BOF/EOF是真实的错误。有什么建议吗?由于

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 

Answer 1

我会改变:

    '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在记录集中没有任何内容，因此您必须删除"rs.Movenext"指令。For语句外面的"Exit For"是没有意义的，所以你也必须删除该指令。