How to write VBA with Do While Loop?

Question

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.

Any help is appreciated!

HansUp · Accepted Answer · 2013-07-11 16:06:09Z

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

Patrick Honorez · Accepted Answer · 2013-07-11 14:49:49Z

0

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

answered Jul 11, 2013 at 14:49

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

1 Comment

nedstark179 Over a year ago

Thanks for the help! I still got the same error though. How does rst.EOF exactly work? Because couldn't it run through the loop while it's on the last record, but then it will try to rst.MoveNext to another record when there aren't any records left?

Collectives™ on Stack Overflow

How to write VBA with Do While Loop?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related