Not Looping through a Recordset

[Novice1]

Instead of looping through the records, I'm getting the same e-mail being sent over and over again. What's wrong with this loop? Thanks

On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iCount As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("qryPCSTrackerWithin120Days")

With rs
If .RecordCount <> 0 Then
rs.MoveLast
iCount = rs.RecordCount

Do While Not .BOF

DoCmd.SendObject acSendNoObject, , acFormatTXT, Me.SubjectEMail, , , "Personnel Data Discrepancy", "We recently noted the following Personnel data discrepancy", False

.MovePrevious
Loop
End If
End With

rs.Close

Error_Handler_Exit:
On Error Resume Next
Set rs = Nothing
Set db = Nothing
Exit Sub

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: LoopRecExample" & vbCrLf & "Error Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit

 

[CJ_London]

perhapsifyoucoudlbebotheredtousecodetagstopreserveindentingicouldbebotheredtotryandreaditandprovideananaswer

 

[pbaldy]

You're using a value from the form instead of one from the recordset, like:

rs!EmailAddress

 

[Novice1]

I'm sorry I don't understand. Still getting the first record sent over and over again.


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iCount As Integer

Set db = CurrentDb()

Set rs = CurrentDb.OpenRecordset("qryPCSTrackerWithin120Days")

If rs.RecordCount <> 0 Then
rs.MoveFirst
While Not rs.EOF

DoCmd.SendObject acSendNoObject, , acFormatTXT, Me.SubjectEMail, , , "Personnel Data Discrepancy", "We recently noted the following Personnel data discrepancy", False

rs.MoveNext
Wend
End If

rs.Close
Set rs = Nothing

 

[pbaldy]

This refers to the form, so won't change:

Me.SubjectEMail

 

[Novice1]

How do I refer back to the record set so it can cycle through?

 

[pbaldy]

Like I showed in post 3.

 

[Novice1]

Thanks. I get it. It works.

 

[Novice1]

Can I use a recordset to find blank fields? The attempt below generated a 2465 error (can't find field)?


Set db = CurrentDb()
Set rs = db.OpenRecordset("qryPCSTrackerWithin120Days")

With rs
If .RecordCount <> 0 Then
rs.MoveLast
iCount = rs.RecordCount

Do While Not .BOF

If IsNull([rs!SubjectEMail]) Then MsgBox "Missing e-mail", vbOKOnly, "Fix Record"

.MovePrevious
Loop
End If
End With

rs.Close

 

[pbaldy]

Is that the correct field name, and is it included in the query?

I'd open the recordset on an SQL statement that only pulled records with no email.