VBA do while returns first record x times (x = # records in rst) 1

[jseptor1]

In over my head again! The code below returns executes the propre number of times in the loop (ie, if there's 8 records, I get 8 messages sent) the problem is all 8 messages go to the first record in the rst!

Any suggestions? I'm pretty weak at VBA, but my guess is that it has something to do with using a string (strSomething) to control the emails being sent.

Dim strTo, strMessage, strSubject, strSomething As String
Dim mydb As Database
Dim myrec As Recordset
Dim I As Integer
Set mydb = CurrentDb

With myrec

Do While Not .EOF
'recipient
strTo = Me.MailTo & "; " & Me.Mobile_Phone & "@messaging.nextel.com" & "; " & Me.Manager_Email
'message
strMessage = Me.FullName & ", daily time reporting is required to receive pay!"
'subject
strSubject = "No daily time has been reported by " & Me.FullName & " for " & Me.txtReportDate
'send mail
strSomething = SendEmail(strTo, strMessage, "", strSubject, "")
.MoveNext
Loop

.Close
End With

'myrec .Close
mydb.Close
MsgBox "complete"
Close

 

[TomCologne]

Modifying this part:

'....
'
            'send mail
            strSomething = SendEmail(strTo, strMessage, "", strSubject, "")
            
         Loop

[b].MoveNext[/b]
'
'.....

should help.


TomCologne
.Close
End With

 

[jseptor1]

I certainly appreciate the quick reply and the confimation of where the problem lies, but how would you recommend fixing this? What's the ideal way to do this?

 

[PHV]

Perhaps something like this ?
Dim strTo As String, strMessage As String, strSubject As String, strSomething As String
Dim myrec As DAO.Recordset
Dim I As Integer
Set myrec = Me;RecordsetClone
With myrec
Do While Not .EOF
'recipient
strTo = !MailTo & "; " & !Mobile_Phone & "@messaging.nextel.com" & "; " & !Manager_Email
'message
strMessage = !FullName & ", daily time reporting is required to receive pay!"
'subject
strSubject = "No daily time has been reported by " & !FullName & " for " & Me!txtReportDate
'send mail
strSomething = SendEmail(strTo, strMessage, "", strSubject, "")
.MoveNext
Loop
End With
Set myrec = Nothing
MsgBox "complete"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jseptor1]

Tom, moving the .movenext outside the loop will force being stuck in the loop. (the loop will never end)

PVH, using !fieldName as opposed to me.fieldName or me!fieldName throws the error "item not found in this collection.

Here's the complete code, with a msgBox taking the place of the send mail command for debugging. This gives me the first record's data 8 times (I have 8 records in the rst)

*********
Dim strTo As String, strMessage As String, strSubject As String, strSomething As String
Dim myrec As DAO.Recordset
Dim I As Integer
Set myrec = Me;RecordsetClone
With myrec
Do While Not .EOF
'recipient
strTo = !MailTo & "; " & !Mobile_Phone & "@messaging.nextel.com" & "; " & !Manager_Email
'message
strMessage = !FullName & ", daily time reporting is required to receive pay!"
'subject
strSubject = "No daily time has been reported by " & !FullName & " for " & Me!txtReportDate
'send mail
strSomething = SendEmail(strTo, strMessage, "", strSubject, "")
.MoveNext
Loop
End With
Set myrec = Nothing
MsgBox "complete"
*********

 

[PHV]

And this ?
Set myrec = Me.RecordsetClone
With myrec
.MoveFirst
Do While Not .EOF

You have to replace !MailTo, !Mobile_Phone, ... with the names of the Fields in the recordset, ie in the underlaying query of the Form.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jseptor1]

I was actually just going down the same path you mentioned, PHV. I realized I was calling the recordset as a query, but building strings based on the form (me.).

So, I realize the error of my ways now. My last question is how do I reference a query field with spaces in it? (ie, the field name is "Full Name"...tried !Full_Name...)

Thanks a ton for the help, PHV

 

[PHV]

![Full Name]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jseptor1]

Thanks for the help, PHV. I'm rustier on this than I thought! Your suggestions worked flawlessly.