vba BOF 或 EOF 为真。在 Do until 中包含 EOF

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

提问by Brian

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.

我的 For Each 中的 rs.Movenext 出现错误。我试过使用直到但不知道把它放在哪里。有人可以建议把它放在哪里吗？不确定在我的 Do until 中使用 EOF 是否会克服错误。

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

按照您的建议 Fil，我在粗体处遇到错误。我不需要使用 i 作为计数器吗？抱歉，我有点困惑，因为这是我无法正常工作的第二个 For Each。据我所知，第一个 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**

Update 2

更新 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

我通过在 rs.movenext 之前执行 aa 而不是 rs.eof 来修复我最初遇到的 BOF 和 EOF 错误。但是当我的 If 为真时（barclays.value = payid(i) 当我尝试将 rs.fields.item(0) 输入到我的范围时，我得到另一个 BOF/EOF is true 错误。有什么建议吗？谢谢

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 

回答by Fil

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.

第二个 for 在记录集中没有任何内容，因此您必须删除“rs.Movenext”指令。For 之外的“Exit For”是没有意义的，因此您也必须删除该指令。