使用VBA遍历记录集 [英] Looping through recordset with VBA

问题描述

我正尝试通过以下方式以轮循方式将销售人员(rsSalespeople)分配给客户(rsCustomers):

I am trying to assign salespeople (rsSalespeople) to customers (rsCustomers) in a round-robin fashion in the following manner:

1. 导航到第一个客户，将第一个SalesPerson分配给客户.
2. 移至下一个客户.如果rsSalesPersons不在EOF，请移至Next SalesPerson；否则，请转至下一个SalesPerson.如果rsSalesPersons位于EOF，则MoveFirst循环回到第一个SalesPerson.将此(当前)SalesPerson分配给(当前)客户.
3. 重复步骤2，直到rsCustomers达到EOF(EOF = True，即记录结束)为止.

自从处理VBA以来已经有一段时间了，所以我有点生锈，但是到目前为止，我的想法是这样的:

It's been awhile since I dealt with VBA, so I'm a bit rusty, but here is what I have come up with, so far:

Private Sub Command31_Click()

'On Error GoTo ErrHandler

Dim intCustomer As Integer
Dim intSalesperson As Integer
Dim rsCustomers As DAO.Recordset
Dim rsSalespeople As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT CustomerID, SalespersonID FROM Customers WHERE SalespersonID Is Null"
Set rsCustomers = CurrentDb.OpenRecordset(strSQL)

strSQL = "SELECT SalespersonID FROM Salespeople"
Set rsSalespeople = CurrentDb.OpenRecordset(strSQL)

rsCustomers.MoveFirst
rsSalespeople.MoveFirst

Do While Not rsCustomers.EOF

    intCustomer = rsCustomers!CustomerID
    intSalesperson = rsSalespeople!SalespersonID

    strSQL = "UPDATE Customers SET SalespersonID = " & intSalesperson & " WHERE CustomerID = " & intCustomer
    DoCmd.RunSQL (strSQL)
    rsCustomers.MoveNext

    If Not rsSalespeople.EOF Then
        rsSalespeople.MoveNext
    Else
        rsSalespeople.MoveFirst
    End If

Loop

ExitHandler:
    Set rsCustomers = Nothing
    Set rsSalespeople = Nothing
    Exit Sub

ErrHandler:
    MsgBox (Err.Description)
    Resume ExitHandler

End Sub

我的表的定义如下:

Customers
--CustomerID
--Name
--SalespersonID

Salespeople
--SalespersonID
--Name

我有10个客户和5个销售人员，我想要的结果是:

With ten customers and 5 salespeople, my intended result would like like:

CustomerID--Name--SalespersonID
1---A---1
2---B---2
3---C---3
4---D---4
5---E---5
6---F---1
7---G---2
8---H---3
9---I---4
10---J---5

上面的代码可用于通过Salespeople记录集的初始循环，但是在找到记录集的末尾时会出错.不管EOF如何，它似乎仍然尝试执行rsSalespeople.MoveFirst命令.

The above code works for the intitial loop through the Salespeople recordset, but errors out when the end of the recordset is found. Regardless of the EOF, it appears it still tries to execute the rsSalespeople.MoveFirst command.

我是否没有正确检查rsSalespeople.EOF?有什么想法可以使此代码正常工作吗?

Am I not checking for the rsSalespeople.EOF properly? Any ideas to get this code to work?

推荐答案

rsSalespeople.EOF不指示您何时位于最后一行，而是指示您何时位于最后一行.

rsSalespeople.EOF doesn't indicate when you are on the last row, it indicates when you are PAST the last row.

因此，当您的条件命中时，最后一个销售人员EOF为假，那么它进行了Movenext(使EOF为true)，则下一个循环的操作是在rsSalespeople的"EOF行"上进行的，您不能从中提取值，因此是错误.

So when your conditional hits the last salesperson EOF is false so it does a movenext (making EOF true) then the next pass through the loop is operating on the "EOF row" of rsSalespeople which you can't pull values from, hence the error.

尝试以下方法:

rsSalespeople.MoveNext
If (rsSalespeople.EOF) Then
    rsSalespeople.MoveFirst
End If

这篇关于使用VBA遍历记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！