SQL 使用 VBA 循环记录集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2579290/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Looping through recordset with VBA
提问by Sesame
I am trying to assign salespeople (rsSalespeople) to customers (rsCustomers) in a round-robin fashion in the following manner:
我试图通过以下方式以循环方式将销售人员 (rsSalespeople) 分配给客户 (rsCustomers)：
- Navigate to first Customer, assign the first SalesPerson to the Customer.
- Move to Next Customer. If rsSalesPersons is not at EOF, move to Next SalesPerson; if rsSalesPersons is at EOF, MoveFirst to loop back to the first SalesPerson. Assign this (current) SalesPerson to the (current) Customer.
- Repeat step 2 until rsCustomers is at EOF (EOF = True, i.e. End-Of-Recordset).
- 导航到第一个客户，将第一个销售人员分配给客户。
- 移至下一位客户。如果 rsSalesPersons 不在 EOF，则转到下一个 SalesPerson；如果 rsSalesPersons 处于 EOF，则 MoveFirst 循环回到第一个 SalesPerson。将此（当前）销售人员分配给（当前）客户。
- 重复步骤 2，直到 rsCustomers 处于 EOF（EOF = True，即 End-Of-Recordset）。
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:
自从我处理 VBA 已经有一段时间了，所以我有点生疏，但到目前为止，这是我想出的：
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
My tables are defined like so:
我的表是这样定义的：
Customers
--CustomerID
--Name
--SalespersonID
Salespeople
--SalespersonID
--Name
With ten customers and 5 salespeople, my intended result would like like:
有 10 个客户和 5 个销售人员，我的预期结果是：
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
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.
上面的代码适用于通过 Salespeople 记录集的初始循环，但在找到记录集的末尾时出错。不管 EOF 是什么，它似乎仍在尝试执行 rsSalespeople.MoveFirst 命令。
Am I not checking for the rsSalespeople.EOF properly? Any ideas to get this code to work?
我没有正确检查 rsSalespeople.EOF 吗？有什么想法可以让这段代码工作吗？
回答by JohnFx
rsSalespeople.EOF doesn't indicate when you are on the last row, it indicates when you are PAST the last row.
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.
因此，当您的条件命中最后一个销售人员的 EOF 为假时，它会执行 movenext（使 EOF 为真），那么下一次循环将在 rsSalespeople 的“EOF 行”上运行，您无法从中提取值，因此出现错误.
Try this instead:
试试这个：
rsSalespeople.MoveNext
If (rsSalespeople.EOF) Then
rsSalespeople.MoveFirst
End If
