如何使用 Do While 循环编写 VBA？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17596385/
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
How to write VBA with Do While Loop?
提问by nedstark179
I have a table with 3 fields: DONOR_CONTACT_ID,RECIPIENT_CONTACT_ID, ORDER_NUMBER. I want to sort DONOR_CONTACT_IDin ascending order which I did with my query Q_RECIPIENT_SORT. Then I want to use temporary variables to check to see if the records have the same DONOR_CONTACT_IDand then display a message if they do (Most of the records have the same DONOR_CONTACT_ID). My program does everything it is supposed to, but at the end it always gets an error that says "No Current Record". Here is my code:
我有一个包含 3 个字段的表：DONOR_CONTACT_ID,RECIPIENT_CONTACT_ID, ORDER_NUMBER. 我想DONOR_CONTACT_ID按我对查询所做的升序排序Q_RECIPIENT_SORT。然后我想使用临时变量来检查记录是否相同DONOR_CONTACT_ID，然后如果它们相同则显示一条消息（大多数记录具有相同DONOR_CONTACT_ID）。我的程序做了它应该做的一切，但最后总是得到一个错误，说“没有当前记录”。这是我的代码：
Option Compare Database
Option Explicit
Function UsingTemps()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strTemp1 As Long
Dim strTemp2 As Long
DoCmd.SetWarnings False
DoCmd.OpenQuery ("Q_RECIPIENT_SORT")
DoCmd.OpenTable ("T_RECIPIENT_SORT")
DoCmd.SetWarnings True
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("T_RECIPIENT_SORT", dbOpenTable)
rst.MoveFirst
strTemp1 = rst!DONOR_CONTACT_ID
rst.MoveNext
strTemp2 = rst!DONOR_CONTACT_ID
Do While Not (rst!DONOR_CONTACT_ID = rst.EOF)
If strTemp1 = strTemp2 Then
MsgBox ("Equal")
Else
MsgBox ("Not equal")
End If
strTemp1 = strTemp2
rst.MoveNext
strTemp2 = rst!DONOR_CONTACT_ID
Loop
Set dbs = Nothing
End Function
I think the problem is with the following lines:
我认为问题在于以下几行：
rst.MoveNext
strTemp2 = rst!DONOR_CONTACT_ID
I think it is trying to move to the next record when there are no more records left. Probably something wrong with my logic. But I've been staring at it for a while and my changes haven't worked. I need another set of eyes to take a look at it.
我认为当没有更多记录时，它正在尝试移动到下一个记录。可能我的逻辑有问题。但我已经盯着它看了一段时间，我的改变没有奏效。我需要另一双眼睛来看看它。
Any help is appreciated!
任何帮助表示赞赏！
采纳答案by HansUp
Consider what happens when your recordset loop is on the last row, and you then do this ...
考虑当您的记录集循环在最后一行时会发生什么，然后您执行此操作...
rst.MoveNext
strTemp2 = rst!DONOR_CONTACT_ID
MoveNextpositions the recordset at EOF--- no record is "current". So, in the next line, the code attempts to store the value from the current row's DONOR_CONTACT_IDto strTemp2. However, since you're at EOF, no record is "current", so Access complains "No Current Record".
MoveNext将记录集定位在EOF--- 没有记录是“当前”。因此，在下一行中，代码尝试将当前行的值存储DONOR_CONTACT_ID到strTemp2。但是，由于您在EOF，没有记录是"current"，所以 Access 会抱怨"No Current Record"。
I think this version will avoid that error. Test the logic to make sure it also does what you need.
我认为这个版本将避免这个错误。测试逻辑以确保它也满足您的需求。
rst.MoveFirst
strTemp1 = rst!DONOR_CONTACT_ID
rst.MoveNext
'strTemp2 = rst!DONOR_CONTACT_ID
'Do While Not rst!DONOR_CONTACT_ID = rst.EOF
Do While Not rst.EOF
strTemp2 = rst!DONOR_CONTACT_ID
If strTemp1 = strTemp2 Then
MsgBox "Equal"
Else
MsgBox "Not equal"
End If
strTemp1 = strTemp2
rst.MoveNext
'strTemp2 = rst!DONOR_CONTACT_ID
Loop
回答by Patrick Honorez
The general idea is as this:
大体思路是这样的：
Set rst = dbs.OpenRecordset("T_RECIPIENT_SORT", dbOpenDynaset)
Do Until rst.EOF
'do or check what you want
'....
rst.MoveNext
Loop
rst.Close
