如何使用do while循环编写VBA？ [英] How to write VBA with Do While Loop?

问题描述

我有3个字段的表： DONOR_CONTACT_ID， RECIPIENT_CONTACT_ID ， ORDER_NUMBER 。我想排序 DONOR_CONTACT_ID 中，我与我的查询 Q_RECIPIENT_SORT 升序排列。然后我想用临时变量来检查是否记录有相同的 DONOR_CONTACT_ID ，然后显示一个消息，如果他们这样做（在大多数情况下记录具有相同的 DONOR_CONTACT_ID ）。我的程序做的一切是应该的，但在最后它总是得到一个错误，指出无当前记录。这是我的code：

I have a table with 3 fields: DONOR_CONTACT_ID, RECIPIENT_CONTACT_ID, ORDER_NUMBER. I want to sort DONOR_CONTACT_ID in 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_ID and 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:

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.

任何帮助是AP preciated！

Any help is appreciated!

推荐答案

考虑一下，当你的记录集循环的最后一行会发生什么，然后你这样做......

Consider what happens when your recordset loop is on the last row, and you then do this ...

rst.MoveNext
strTemp2 = rst!DONOR_CONTACT_ID

的MoveNext 位置记录在 EOF ---没有记录的当前。因此，在接下来的行中，code尝试存储值从当前行的 DONOR_CONTACT_ID 到 strTemp2 。但是，因为你在 EOF ，没有记录的当前的，所以访问抱怨的没有当前记录。

MoveNext positions 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_ID to strTemp2. However, since you're at EOF, no record is "current", so Access complains "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

这篇关于如何使用do while循环编写VBA？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！