Help with looping through records (1 Viewer)

[cricketbird]

I'm baffled by why this code I've used many times isn't working on a new report. It should loop through each record and print one report per record. But, I can't even get it to loop through the recordIDs... What am I missing? This is in an onclick event of a button.

Set db = CurrentDb()
Set RS = db.OpenRecordset("SELECT EmailID, Email FROM [emails] WHERE [ACTIVE]")

If Not (RS.EOF And RS.BOF) Then
    RS.MoveFirst
    
 Do Until RS.EOF = True
    Debug.Print Me.EmailID
'   SendPDFReportAsEmail 'Currently commented out for testing
    RS.MoveNext
 Loop
Else
    MsgBox "There are no records in the recordset."
End If

RS.Close 
Set RS = Nothing

In the Immediate window, I get "1, 1, 1" on three lines instead of what should be "1, 2, 3". Why is this not looping?

 

[The_Doc_Man]

I don't see anything obvious either, but this code looks like it is simple enough for you to put a breakpoint on the "Set RS=" line and thing single-step through the code to see exactly what is happening.

 

[plog]

You're printing out a value on the form, not a value from the record set.

 

[arnelgp]

You're printing out a value on the form, not a value from the record set.

this is true, you need to change your code:

 Do Until RS.EOF = True
    Debug.Print RS.EmailID
    '   SendPDFReportAsEmail 
    'Currently commented out for testing
    RS.MoveNext
Loop

 

[CJ_London]

Actually that should be RS!EmailID

 

[Gasman]

Learn to walk your code with F8 and check your variables.

 

[cheekybuddha]

Actually that should be RS!EmailID

Or possibly:
RS.Fields("EmailID")

 

[CJ_London]

Less typing with !

 

[cheekybuddha]

Less typing with !

Can't argue with that!

 

[xavier.batlle]

Or possibly:
RS.Fields("EmailID")

Another one: RS("EmailID")

 

[MarkK]

If you use a With block...
• you don't need a recordset variable

Also, when you first open a recordset, if it...
• contains no records, .EOF will be true.
• contains one or more records, the first record will be current.

Therefore, you don't need to...
• check .BOF, or
• .MoveFirst

So you can lean up your code to...

    With CurrentDb.OpenRecordset("SELECT EmailID, Email FROM emails WHERE ACTIVE")
        Do While Not .EOF
            Debug.Print !EmailID
            .MoveNext
        Loop
        MsgBox "Loop handled " & .RecordCount & " Row(s).", vbInformation
        .Close
    End With