CODEWITHSUNDEEP
ms-accessloopsvba
nedstark179
nedstark179

Reputation: 572

How to write VBA with Do While Loop?

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!

Upvotes: 2

Views: 36364

Answers (2)

HansUp
HansUp

Reputation: 97101

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

Upvotes: 2

iDevlop
iDevlop

Reputation: 25252

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

Upvotes: 0

Related Questions